Google Sheets - JSON API, Wep App - e.j1 p.1

Learn how to create self-made JSON API from Google Sheets using a Web App and make AJAX requests from external page.
Full Series Playlist • Google Sheets Self-Mad...
#googlesheets #json #api

[@bang-naim]

I learn a lot. I have been rewatching this multiple times on multiple occasions. You're great at teaching, thank you so much.

[@getitdonetube]

Thank You!

[@stefanbirsan3320]

For those who have troubles with CORS. My issue was that I copied the wrong url to fetch from. The interface is a bit different now (JAN 2023). You'll want to go to DEPLOY -> MANAGE DEPLOYMENTS -> Copy URL for web app (ends in /exec). I had issues because I went to TEST DEPLOYMENTS and that url ends with /dev. And it has a different id. Hopefully this helps.

[@getitdonetube]

Thanks for sharing!

[@stefanbirsan3320]

@@getitdonetube Thanks for your tutorial!

[@macreations0528]

I tried with dev it was throwing same CORS issue.

[@AlexM-ym7kd]

You are a very good teacher, I appreciate these videos. It's funny that you're so good at this stuff that you can't get it to not work 🤣 8:55

[@umeshagarwal4271]

I have been watching your videos, it's a great learning. Thank you so much for all the hard work you did. Blessings from India🙏

[@usamanaeem8737]

Dude you are brilliant

[@ashimov1970]

Dude, you're the best. God bless

[@getitdonetube]

I appreciate that!

[@66Komodo]

it is interesting to see what is possible with google sheets. cool videos. Please keep going. Thanks

[@GVensino]

Thank you! Saved me! This video is amazing. God bless you :)

[@caizza3]

Great video, i really like how you go through the documentation!

[@sharonwan9813]

Thanks a lot for this clear and concise step by step video!

[@desenvolvimentonavalcare3846]

Why did you put your JSON variable inside an array? at 1:07. Could it just be a JSON? Also, thank you SO MUCH for this video series bro.

[@88gomo88]

I'm following your videos and I learing a lot. Thank you! About this json api, how can I work if my API can be executed only by users from my domain? I guess i need a kind of google sign in function. Do you have any suggestions?

[@soyezcloud]

Why are you not using git to push changes to GitHub from vscode ?

[@mateuslidonis]

Is there any kind of usage limit for this API? Can I use and request the google sheets info as many times as I want?

[@jhicinternational]

excellent

[@sudoalex]

Thank you very much for doing these videos!

[@phecdu]

Thank you so much. I think you can make a seri of google sheet API. That would be interesting. And you can share your code in git so every one can easy follow

[@sistana]

Very clear, thank you very much. 100% high quality information. But how to avoid CORS restrictions with AJAX requests from other servers?. Thanks again

[@getitdonetube]

The only time I get CORS error is when I have some sort of problem in the code. Otherwise I'm able to call it from other servers just fine.

[@stephankuche5859]

Thanks a lot very helpful .

[@asifiqba1pa1ash]

can you show me how to interat withj shopify api with google sheet api? i want to append a row when any order creates, I want to show them on a google apps script web app, then there will be a button that sends shopify to mark an order fulfilled and notify customer to pickup from the store

[@NickStoneDevelopment]

AJAX Works as long as you either host on same source or don't have to use any JWT or CORS requirements. With Google, it's hard to believe you can securely store or get data without having to involve Google APIs and Google Console, generating API keys, scopes, app keys, etc. Is this just for public to public files and access?

[@tazulislam2698]

Thanks! Please make a video on how to insert 2d array of value?

[@dwadd7528]

how do i fix CORS error? { mode: 'no-cors'} does not return JSON.

[@getitdonetube]

That usually means you have an error in your code.

[@stevepineirov20]

Awesome video’s love them!!

[@garylee5170]

So like I’m was saying to myself and Thanking to myself is what tha different of HTML and JSON and CSV?

[@getitdonetube]

CSV is a text file where columns are separated by commas & rows by a new line. HTML - web markup language JSON - JavaScript Object Notation, which is text formatted in a specific way to represent data en.wikipedia.org/wiki/JSON

[@monarca2846]

Why I did not find this before?! Tks!

[@philipevangelista3020]

Great tutorial, thank you! This may be not related to your video but I'm trying to find out more about cookies and session data with the use of GAS. Unfortunately, I couldn't find anything on the interweb that would "mimic" storing session data of a web app (made with GAS). I want to store some sort of session cookie so that when a user logs in (or refreshes/goes to a different page), the session data is persisted through other pages as well (i.e. Names, Age, Reg info, etc). I hope that made sense and if you have any tips on this, would greatly appreciate it! Thank you!

[@getitdonetube]

Here is all you need kzbin.info/www/bejne/hmfVqaivg7lqitk

[@hamidallahzakarya7224]

subscribed

[@daytodatainc.2520]

Great video!

[@dwburger]

This is a very helpful video for a beginner like me. Just wondering...how can I console.log the response in the doGet function? I'd like to console.log the response from this return: return ContentService.createTextOutput(JSON.stringify(response)) .setMimeType(ContentService .MimeType.JSON); Thanks for any help or guidance you can provide!

[@getitdonetube]

Watch this kzbin.info/www/bejne/nJ3bm2CHd5pjsNE

[@karthikpangala9854]

Thank you.

[@normanejm]

HI, but how can it works for a external account?? I mean, I'm not the "owner" of that sheet, but I have to read the sheet...

[@getitdonetube]

I'm not exactly sure what environment you are in, but you can find examples here developers.google.com/sheets/api/quickstart/js For different environments check the left menu.

[@PhamTienPhong]

Does it expose all data to the network so anyone with WireShark can read all the data?

[@getitdonetube]

The end point is HTTPS, so it should be encrypted.

[@PhamTienPhong]

@@getitdonetube thanks for clarifying.

[@tazulislam2698]

Does Anybody knowes how to send 2d array to google sheet?

[@getitdonetube]

What is the difference? So long as you know how to work with 2d arrays, just pass them the same way and handle them on the other end.

[@roktinfast]

I can not get this to work. I had some code that communicated with a google sheet, using jsonp. It stopped working a month ago, getting CORS errors. I tried your version, but cant get it to work either. Could you perhaps revisit the subject in a new video, please?

[@getitdonetube]

I'm not sure, I've been hearing people telling that this doesn't work forever, yet every time I try it seems to be fine. I can tell you I've just tested one I had done and it works just fine for me today. I find that most of the time when people get CORS errors it's because the script returns an error instead of returning a valid JSON result. That being said, the issue here is that browsers auto-update for most people, so Chrome, Firefox or whatever browser you use could introduce some new policies and it may stop working. Not to mention that people also add all sort of extensions to their browsers.

[@gergelykovacs6577]

@@getitdonetube Unfortunately It doesn't work for me either. I am producing a nice json output on the server, I can open it in the browser. I get the CORS error, whet trying to fetch from the page. I tried setting options to 'no-cors', and stuff, but it didn't help. The code is exactly like you showed though, with the difference that i am using a separate parameter for the json data output, like: '?v=json' (because i have multiple views). I am a little desperate.

[@gergelykovacs6577]

My workaround: I have added fetch(url, {mode: 'no-cors'}) in the function definition, then on the end: .catch(function () { console.log("Promise Rejected"); This at least gave me an answer without red errors: 'Promise rejected'. Is there an option to set "Access-Control-Allow-Origin" : "*", on the server side?. I can't get around this.

[@gergelykovacs6577]

I can even get the json response with curl , if I am followind redirects with -L flag, ang using exec version , not development version. But the client page still not able to get the response :(

[@gergelykovacs6577]

I have even created a new very simple test project putting out a very simple one liner json on the browser. Still doesn't work from client side . Ia am only ale to log errors on the console, which is keep telling me about CORS error, and that 'No Access-Control-Allow-Origin header' is given. I was trying on latest Safari and Brave. Since these are quite quite advanced browsers, it should work with these.

[@findthetruth3021]

Could you please make a cascade drop-down in HTML alone or anything that works?

[@getitdonetube]

I just did a 2hr video on cascading dropdowns which is published on the channel.

[@georgejacob9891]

I was wondering if I could ask you a question about the fetch API. I am doing a post method using the fetch API on the client side. However, when I deploy the U app through Google sheets, I get the following error: (Reason: CORS header ‘Access-Control-Allow-Origin’ missing). Status code: 200. This is my code: fetch(url, { method: 'POST', // *GET, POST, PUT, DELETE, etc. mode: 'cors', // no-cors, *cors, same-origin cache: 'no-cache', // *default, no-cache, reload, force-cache, only-if-cached credentials: 'same-origin', // include, *same-origin, omit headers: { 'Content-Type': 'application/json' // 'Content-Type': 'application/x-www-form-urlencoded', }

[@getitdonetube]

remove mode: 'cors', // no-cors, *cors, same-origin cache: 'no-cache', // *default, no-cache, reload, force-cache, only-if-cached credentials: 'same-origin', // include, *same-origin, omit

[@getitdonetube]

and 'Content-Type': 'application/json'

[@getitdonetube]

So long as you have a valid request it should work fine.

[@georgejacob9891]

Thank you so much! That worked. I am so grateful to you for creating these amazing videos. It allowed me to do so many things that I could not have done otherwise. You are an amazing teacher!

[@UTJK.]

Why don't you share your code through github?

[@ricardocnunes]

What code editor are you using? Thanks

[@daytodatainc.2520]

VSCode

[@somanathbhat5517]

Is there a way to to add some sort of authentication, so that not everyone can access the spreadsheet API. It would be great if have videos that cover it it or post links that cover it.

[@getitdonetube]

There is certainly a way, but if you need it done properly, it's really not simple. You will need to create your own system of generating and handling JWTs (JSON Web Tokens). If you need something simple you can just create a key that you can check if it's valid on request. But this is not safe for serious work, since it's vulnerable to the key being stolen or hacked. I have seen worse solutions though :)

[@somanathbhat5517]

@@getitdonetube Your video was very clear and helpful. It was exactly what i was looking for. Thank you for the prompt response.

[@sudoalex]

You made a typo in your hashtag, you typed josn instead of #json

[@getitdonetube]

Thank You! :)

[@NickStoneDevelopment]

JAVASCRIPT object notation

[@deepakkoirala2296]

Is this free? Is there a limit?