Learn how to work with Google Sheets API & Google's NodeJS JavaScript library. In this tutorial we'll read & write data to our Google Sheets spreadsheet using our Node JS app. Node JS Google Sheets API Tutorial - Part 1 #GoogleSheets #API
taking a minute to thank you for this video. Spent 2 days over the internet failing each and every time. In 30m of video, I got it to work !! thank you so much !
I don't know why I only got this now. I've been wrestling with Google Sheets API start up but once I got the response that I want from my sheet by following along. I smiled like a kid. Thanks man!
Thank you very much! I had no idea what i was doing, but it works anyway. Now I can try to find a way to get data from Shrimpy (or binance) and write it back to my Google Sheet. Very well explained and I hope to find more of your video's.
Range related FYI: although I can't totally explain how it's the case, when entering the range, it only accepted the pure x/y sheet values without any "Data!" prefix allowed.
Thanks for this! My question is - how did you find that alternative way of connecting to the GSheet with keys.json if there is no documentation on it? I am interested in your thought process
Good question. I have no clue, just based on experience from other libraries and docs for other languages. Sometimes you'll find something in their docs for Python that is not listed in JS section and stuff like that. And then hopefully you can piece it together. You just try stuff and sometimes you make it work, other times you don't.
@@ExcelGoogleSheets Hmm seems interesting. Then it is based on experience, which must be acquired and not learned. Thanks for the reply and keep it up!
Thanks for this great tutorial, I'm getting so much out of it. Just running into one snag: when I run the first several lines of code to test the connection, it doesn't log either 'Connected!' or the error.
UnhandledPromiseRejectionWarning: Error: Invalid Credentials -- Hi I'm getting this error when running the gsrun. Do you have any idea how to fix this?
Data! refers to the name of the worksheet. You should match it to your worksheet name. If your worksheet is called Sheet1, then you do Sheet1!A1:F30, instead of Data!A1:F30.
@11:39 how do I download the file after creating a service account? For instance when I am running VS code on a second machine and don't have access to the keys JSON file?
Great tutorial just one question. how can you update a specific cell without hardcoding the range. because the sheets api doesn't send the cell range in the response
I have done exactly as instructed. The first time I had no problem at all, I connected to the API as you did. Now, for whatever reason, I am running the code and the only thing that appears in my terminal is "conectado!" because of this function: client.authorize(function(err){ if(err){ console.log(err); return } else { console.log('Conectado!') } }) ------ No syntax erros, no nothing. I have granted access from the sheets archive to the console.dev email I have created and yet, nothing is happening :( the full code I'm running is: const {google} = require('googleapis') const keys = require('./keys.json') const client = new google.auth.JWT( keys.client_email, null, keys.private_key, ['www.googleapis.com/auth/spreadsheets'] ) client.authorize(function(err){ if(err){ console.log(err); return } else { console.log('Conectado!') } }) async function gsrun(cl){ const gsapi = google.sheets({version:'v4', auth: cl}) const opt = { spreadsheetId: '*censored*', range: 'engenharia_de_software!B4:H27' } let data = await gsapi.spreadsheets.values.get(opt) let dataArray = data.data.values let newDataArray = dataArray.map(function(r){ r.push(r[0] + '-' + r [1]) return r; }); const updateOpt = { spreadsheetId: '18PfgKhFDFVyFQC49BVsxAvXdM_kL3L9neGJ2n23rtqE', range: 'engenharia_de_software!G4', valueInputOption: 'USER_ENTERED' resource: { values: newDataArray} }; let res = await gsapi.spreadsheets.values.update(uptadeOptions) console.log(res); }
Hey, Thanks so much! I tried to run on web hosting and have an error when i connecting to Google sheet: "Error: error:02016002:system library:stat:No such file or directory" Did you test this app on live hosting?
hi, any advice for error: "Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential." The process for creating credentials in the google cloud console is a bit different now and I have tried a few different things but nothing works. Any help appreciated!
I got that error too :( . I am able to connect but once I try to get the data I get the error: Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential appears
Ok so you are not going to use the json file from the API itself. You have to go to credentials->create credentials-O auth client ID. After you finished the Oauth2 stuff, you eventually get another .json file. Then you follow the guide for developers on google. developers.google.com/sheets/api/quickstart/nodejs?hl=en_US in the guide they rename the Oauth2 json file as credentials.json For the Oauth2 stuff I followed a youtube video called, How to create Google Oauth2 web application credentials in 2021 by DAIMTO Developer Tips But yeah, forget about the API json object from googlesheets api. In the documentation, there will be a file called token.json. That file gets autogenerated/altered after running the file the first time around, but you can create an empty token.json file just to feel better. Also putting the url and redirect uri in the Oauth2 client id screen can be annoying. For the javascript orgins url I just use the localhost:3000 link. If you run it without the uri, you'll get an error telling you what your uri should be so you go back and enter it and get a new json file. mine was just the localhost:3000/oauth2callback thing with the http in front. The first time you run the file, it will open the browser and ask you to sign into google. Doing that will fill out the token.json file so it doesn't ask again.
Great video. How will you create a workflow so that any user can get authenticated and select her own Google Sheets? Like having a button on a front-end app, where people click to authenticate themselves and then have a little form or something where they can enter the Google Sheet id. etc. I'm concerned about the authentication part. In your video, you generated an email that has to be used to share a Google Sheet, but I don't want to do that.
Hi.. i am getting the below error : Error log: Error: invalid_grant: Invalid JWT: Token must be a short-lived token (60 minutes) and in a reasonable timeframe. Check your iat and exp values and use a clock with skew to account for clock differences between systems.
My code keeps saying "No key or keyFile set", which indicates invalid key according to google. I did download a service account Json(and not client ID); I also deleted then recreated my service account again but still no luck :(.
Does anyone know how to access another person's spreadsheet? My coworker has the authority for the company's spreadsheet but I can't access with Node.js since it is restricted only to the employees. Does anyone know how to bypass that? Like if I can sign in my work account and then use that to access the spreadsheet?
Thanks for this video! Much appreciated. I do have a question, do you perhaps know why after I run a command, all that is returned is "Connected!' and nothing else?
it works now. code was ok. i dont know why it havent worked. I received this message error before: "Your message wasn't delivered to serviceman11@node-js-gs-api-test-XXXXX.iam.gserviceaccount.com because the domain node-js-gs-api-test-XXXXX.iam.gserviceaccount.com couldn't be found. Check for typos or unnecessary spaces and try again. " it works now. havent got any error msg.
Great tutorial congratulations! I'm having problems with the data.data.values , because I'm trying to store that value to a variable that use later in my code, but I think that the async function is running later than the rest of the code.... so I get and empy value of my variable
I tried to to this. Maybe it will be helpful to do like this: (In main async IIFE you get receivedData from spreadsheet and then, still in this IIFE you can do different things with the data) const { google } = require("googleapis"); const keys = require("./keys.json"); const client = new google.auth.JWT(keys.client_email, null, keys.private_key, [ "www.googleapis.com/auth/spreadsheets" ]); const gsrun = async cl => { const gsapi = google.sheets({ version: "v4", auth: cl }); const opt = { spreadsheetId: "1NabcyX9jnAGQCZjnylhKSc8T30SeoeDS5CUudQoqZvI", range: "Arkusz1!A2:B5" }; const data = await gsapi.spreadsheets.values.get(opt); const dataArray = data.data.values; return dataArray; }; // main async IIFE (async () => { client.authorize(function(err, tokens) { if (err) { console.log(err); return; } console.log("Connected"); }); const receivedData = await gsrun(client); // you can use receivedData here later: console.log("later", receivedData); })();
hello, thanks for the video it was really helpful. Please I want to get the data as objects not arrays of each row. for example: {"id": "1212","name": "Nabil"..}. In your example, each row is in an array. Is there a way to change the response to get data as I want?
You can convert yo array of objects yourself. There is an example how to do it both ways in this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0KuSD5PGQF8.html
UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1) (node:14728) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code. this error appears at this point ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MiPpQzW_ya0.html It seems to be related to NodeJS. What to do ?
@@ExcelGoogleSheets I'm not the original poster here, but had the same issue. What specifically do you mean by "test the connect"? I was able to get the console log "Connect" message fine before the gsrun function.
Not directly from API. You'll need to write the JavaScript to convert the data. You can see an example of that if you watch my SUPERSQL function videos.
@@ExcelGoogleSheets I was able to get some help from a friend this worked for me function fixArr(apiRes) { const keyValue = apiRes.shift() const persons = []; let result = {} for (let i = 0; i < apiRes.length; i++) { for (let j = 0; j < keyValue.length; j++) { result = {...result, [keyValue[j]]: apiRes[i][j]}; if (j === keyValue.length - 1) { persons.push(result); } } } return persons } var googleData = fixArr(parent.data.values); console.log(googleData)
Hi thank your video i have mistack when i do node main.js, "(node:7156) UnhandledPromiseRejectionWarning: Error: Unable to parse range: Data!C23:C27"b - thanks your help
links on the screen but missing from the description: - nodejs.org/ - code.visualstudio.com/ - developers.google.com/sheets/api/quickstart/nodejs - console.developers.google.com
Not by default. There might be some library in Node that helps make asynchronous functions synchronous but it kinda defeats the whole purpose of using Node. I would say if you want to use synchronous code use Python instead.
@@ExcelGoogleSheets I want it to be synchronous to make a loop that prints data from the cell in ascending order every 10 seconds. EXAMPLE: A1, wait 10 seconds; A2, wait 10 seconds; A3, wait 10 seconds ... I couldn't make it synchronous, it prints the cells out of order
Classic comment from people who don't regularly use spreadsheets :) "Data" is the worksheet name. If your worksheet is called Sheet1 then it should be Sheet1!A2:B5, if it's called Amazing Code then it would be 'Amazing Code'!A2:B5 Otherwise by changing to A2:B5 it will grab it from the first worksheet, which in the future may end up being not the first one and your code will fail.
why am i get this error: TypeError: Class constructor JWT cannot be invoked without 'new' on: const client = google.auth.JWT(keys.client_email, null, keys.private_key, [ "www.googleapis.com/auth/spreadsheets"]);
