Google Sheets API - JavaScript NodeJS Tutorial
Вставка
- Опубліковано 23 тра 2019
- 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
This man so nicely explained the whole thing that I couldn't figure out when the 45 minute is over... Absolutely awesome...
Great tutorial! Thanks so much!
Could not figure out a few things with Google's confusing documentation. This is exactly what I needed.
Thanks for the tutorial. I searched a lot and all I found wasn't nearly as clear and simple as this. It worked like a charm!
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 !
As a JS novice I found your video extremely helpful and easy to follow as you explained every detaile so clearly. Thanks!
Exactly what I need it.
A way to communicate with specific spreadsheet
Thank you!
Awesome video! So helpful! One of the best JavaScript tutorials out there
Very helpful from the beginning until the end ! Thanks for the video!
I really enjoy your videos, you help me out a lot!
this video really save my day! many thanks to you!
Muuuuy bueno, gracias!! La parte de autenticación con JWT esta mucho más simple y práctica que como lo plantea la documentacion oficial de Google.
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.
Thanks for getting me started with Google Sheets
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!
👍
Nicely explained video. Love the live debugging
This was incredibly helpful! Thank you so much!
Thank you for this tutorial!
This is so so so helpful. Thanks a lot!
You really know how to explain node js with google sheets! Congratulations and continue!
Very nice explanation, helps a lot, thanks.
Sir, Your Tutorials are very helpful, thanks #learnGoogleSpreadsheets
Awesome video! Keep it coming!
Thank you so much! Excelent tutorial!!!
Worked perfectly. Thx.
Thank you, very well explained
thanks a lot man, nice tutoriasl keep it up.
Excellent! Thank you!
Great introduction!
Thanks dude, u helped to solve my problem :)
thank you very, VERY much!!
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.
Thank you. Working!
Really an awesome video.
Very helpful!! Thanks
That was very very helpful. This is the only video that explains how to pull and overwrite the data without these third party apps.
:)
Very nice video, just a quick question.
1. Can we be able to pull only the rows that were modified?
2. Can we get the first sheet automatically?
excellent tutorial thank you
GREAT VIDEO !
You are just awesome!
Thanks. Finally I'm able to do it. It works.
👍
Hi men, this is amazing, you have a blog or something? Can you share the code?
Clearly explained. Thank you!
Glad you liked it
great video
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.
Great!
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.
It should be the name of your worksheet. If your worksheet is not called data you should match whatever it's called.
Thank you! I was having a problem parsing the data, but that worked!
@@susannasmith4475 same! thanks James
thanks this is really helpfull
wow thanks mate, you give free amazing lecture
Glad to hear that
Thanks so much! This worked very well.
Great to hear!
is there a way to get the font color and cell background color with JS? can`t figure it out for now
Thank you very much!!
Amazing!
👍
Thank you!
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!
I have some question can I get a cell style with value such as fill-color of cell ?
Thank a lot!
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
Great tutorial, However it looks like my code is not refreshed when I add/remove some data in my sheets !
Any tips ?
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 :(.
como le para agregar únicamente la concatenación? Gracias de antemano y saludos cordiales.
What VC extension you have that reads the content of your required packages and does autocomplete on hover? (Great video btw)
I don't recall installing any extensions for that. Doesn't it do that automatically?
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 looks like you forgot to call the function after console.log("connected");
@@ExcelGoogleSheets I have the same problem. It doesnt work for me. Here is the code:
const {google} = require('googleapis');
const keys = require('./keys.json');
// nodejs.org/
// code.visualstudio.com
// developers.google.com/sheets/api/quickstart/nodejs
// console.developers.google.com
// developers.google.com/identity/protocols/oauth2/scopes
const client = new google.auth.JWT(
keys.client_email,
null,
keys.private_key,
['www.googleapis.com/auth/spreadsheets']
);
client.authorize(function(err,tokens)
{
if(err)
{
console.log(err);
return;
}
else
{
console.log('Connected!');
gsrun(client);
}
});
async function gsrun(cl)
{
const gsapi = google.sheets({version:'v4', auth: cl});
const opt = {
spreadsheetId: 'XXXXXXXXXXXXX',
range: 'Data!A1:B5'
};
let data = await gsapi.spreadsheets.values.get(opt);
console.log(data);
}
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.
For the people watching now, for the range part, just take out the "Data!" from the beginning of the range to make it work for you.
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.
Thank you so much. How can i use delete row.
please also explain batchGetByDataFilter i want get filtered data by id . i have first column is id and want to get all data belong to similar id .
helpful!!
U r the man!!!
:)
Great video. How do I keep keys.json file safe in a web app?
I have the same question. I was trying to put them in .env, but it's not working
I keep getting this error: TypeError: Cannot read property 'data' of undefined
Any reason why you think this is happening?
this is sheet name
Works when you change Data! for Sheet1!
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 ua-cam.com/video/0KuSD5PGQF8/v-deo.html
Hey, how can I make borders for cells with this?
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);
}
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
You won't be able to access it outside of that function. Elsewhere you can pass it as an argument to a different function.
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);
})();
@@jurekskowron8192 thanks a lot!!!
@@juancruzmoras4730 De nada
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?
@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?
UnhandledPromiseRejectionWarning: Error: Invalid Credentials
--
Hi I'm getting this error when running the gsrun. Do you have any idea how to fix this?
i'm having the same trouble too
I am facing the same problem, ¿have you solved it?
20.45m... i have write "node main.js" on the terminal, but do not function...what should be the problem?
Be sure that you type "node main.js" on the same directory that you create the "main.js" file, otherwise it will not work ;)
great one thanks. can you share some the code source ?
How to send all sheets from within Excel in one go?
And modification to the code
let ws = excelFile.getWorksheet('subtotal');
range:'data!A2',
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.
Use the actual example in Google Sheets API documentation to do that.
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?
how to get changes in sheet dynamically to json
Can this be run automatically when the Excel sheet gets updated?
You can add a macro in your excel file to run a shell command on change or probably better on save and execute the node command.
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 ua-cam.com/video/MiPpQzW_ya0/v-deo.html
It seems to be related to NodeJS. What to do ?
The API call failed. Could be millions of reasons. Did you test the connection to make sure it works before calling the API?
@@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.
I was getting an error "unable to parse range". Changing the range value from 'Data!A1:B5' to just 'A1:B5' fixed it.
Data refers to the name of the worksheet. So you should change it to your worksheet name.
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.
@@stevenismart thank you so much! I will try this
Hi Nice video can we be able to update values using this app?
Sure.
I have this that said gaxios login required how do i fix that ?
Error log: Error: invalid_grant: Invalid JWT: Token must be a short-lived token (60 minutes) and in a reasonable timeframe.
Kindly,you should put the link of installation video or whatever you refer to visit us in description
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
Do you have a worksheet in your spreadsheet called "Data"?
not able to access spreadsheet... after successfully creating it by APi.. it says access denied.. Any Fix?
it returns at testing main.js at 20.45 an error { FetchError: request to www.googleapis.com/oauth2/v4/token failed, reason: connect ETIMEDOUT 216.58. etc
It seems like you may have skipped a step in developers console.
is it posible to get the headers like ID and Name as keys in the array like [id: "1", name: "anna"] and so on?
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)
What would you use this for?
If nothing else you can use it in cases when your script times out in Google Sheets when things get a little too complicated. This will allow you to pull data, process on your own machine for as long as you want and then write it back when it's done.
automate the workflow of course
Hello, very good video! How can it be done with a synchronous function?
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
Try this www.npmjs.com/package/deasync
Google API no longer automatically downloads Json file. So I'm lost after that.
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"]);
try
const client = new google.auth.JWT(keys.client_email, null, keys.private_key, [ "www.googleapis.com/auth/spreadsheets"]);
HI! I'm getting this error. Any ideas?
(node:94949) UnhandledPromiseRejectionWarning: Error: Unable to parse range: Data!A1:B5
at Gaxios. (/Users/home/Desktop/Sheets/google-sheets-node/node_modules/googleapis/node_modules/gaxios/build/src/gaxios.js:73:27)
at Generator.next ()
at fulfilled (/Users/name/Desktop/Sheets/google-sheets-node/node_modules/googleapis/node_modules/gaxios/build/src/gaxios.js:16:58)
at processTicksAndRejections (internal/process/task_queues.js:97:5)
(node:94949) 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(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:94949) [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.
What's the name of your worksheet?
For Privacy issues I'll need to append it a bit: Sheet: "TEAMNAME Project Folders Builder 202_MYNAME". My script is "spreadsheets.js
@@ExcelGoogleSheets Got it!! Google must have updated something.
range: 'Data!A1:B5' doesn't work
range: 'Sheet1!A1:B5' works!!!!
You should just use the name of the worksheet. My worksheet was called Data so I use Data, yours is called Sheet1, so you use Sheet1.
Any ideas why it is not reading my keys file ./keys.json and then unable to find the email or the keys
{keys} it needs to be just keys missed that typing too fast
I get a "google.sheets is not a function" error. Has anything changed on this since the release of this tutorial?
It should be the same.
@@ExcelGoogleSheets oh god...off to find that missing comma then...Thanks!