ฝัง
- เผยแพร่เมื่อ 16 มิ.ย. 2024
- If you’d like to use the data in your Google Sheets projects from your Node applications, this is the guide for you.
We’ll use the google-spreadsheet module (www.npmjs.com/package/google-...) by Theo Ephraim ( / theozero ) to access an example spreadsheet inside of Google Sheets.
Check out Twilio at: twilio.com? - วิทยาศาสตร์และเทคโนโลยี
🔥 WATCH NEXT: Build an Animal Crossing price lookup bot with Google Sheets and WhatsApp th-cam.com/video/eqtQ76X_owQ/w-d-xo.html
Hey!, i have a question, how can you throw an error message, if the query fails?
Not only is this video informative, it could be the gold standard for all programming demonstration videos. Tight editing, brisk pacing, considerate of the viewer's time. Nice!
Thanks for watching Rendall and for the kind words!
Let us know if there's any videos you want to see us make. We'd be happy to oblige.
@@twilio I would love to see a video exploring methods of managing state in an interactive text messaging session or phone tree, especially if it could persist over time, perhaps days.
Literally the best tutorial on Spreadsheets + Node.js topic. Kudos, folks you nailed it. Give that man a raise! 👌
Thanks for watching Ahmad! Let us know if there's ever a video you want us to produce.
For anyone stuck not being able to download the client secret JSON file, it's changed since the video was made. After creating the service account click on it in credentials, go to keys, and then select "add key" and it will download.
Thanks for this.
Thank you🙏
this is the best programming tutorial i have ever seen, dude you are awesome
dang this man gets straight to the point and shows all of what you probably would want to do, very epic.
Love this format of vids. Simple, quick and dynamic. You just got another sub.
To the point, concise & very well delivered. I loved everything about this video. Thank you team Twilio
Thanks for the kind words... we're so happy you loved it!
Got a Google Sheet that you need to access from your code? Let us know if this guide helped you get it done!
Nice guide! I'm just a newbie with js and Node and wanting to start on React. If anything, it'd really help making a vid since there're no React-Google Sheets API out there at all
but is safe to use?
The tutorial was awesome. Thank you. The only thing missing is how to color rows, that would have been all I needed for my project! Thanks once again.
Gold standard for tutorials.
Really great video. Compact and informative. Easy to QuickStart exploring the api
Thanks for the kind words and thanks for watching!
Really good video on a nice topic, definitely bookmarked for later use when I am planning new NodeJs projects ^_^
Have a great day!
This video should be the new gold standard for how to introduce any new API by video.
And it’s not even Twilio’s API!
This was brilliant. I'm new to Javascript and have been looking everywhere for an accessible guide to interacting with Googlesheets. This was simple, effective and SUPER informative. Thanks! One thing to note, the code throws an error in the most recent version of Java. I had to install 2.0.6
Glad it was helpful Michael!
Came here looking for copper and found gold, great tutorial!
Happy to help! Let us know if there’s more gold we can help you mine.
this video just saved my career
thank you
Fantastic Tutorial! Was able to combine this with a chrome extension to pull data from the dom and reduce time spent by 90%. Thank you! Ran into an issue where rows are added in random order. Are you familiar with a way to order rows when writing to csv?
This tutorial is outstanding! Keep up the great work!
Thanks for watching and for the kind words!
@@twilio Do you happen to have any documentation on querying rows that match a certain condition? I can't seem to find anything on it. Thanks!
I believe batchGetByDataFilter will get you what you need: developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGetByDataFilter
I watched your amazing Pyhon + GSheets video and made an application that tracks stock prices. I want to learn JS some time in the future, so I probably will come back to this video then! Thank you!
I’m glad I could help! Let me know what you build 😀
@@twilio this channel is not run by a single person, right? The production and video quality is impeccable!
@@weyauh4338 Hey, I work for Twilio and Brent is in fact behind the majority of the technical videos you see in this channel. I'm hoping to learn from his techniques and start producing videos myself, but it is a bit intimidating!
How I can add multiple queries? any idea?
The greatest tutorial thank you so much
Superb! Awesome video. Thanks a lot for sharing! :)
You’re welcome, thanks for watching!
Great tut, but one improvement: When calling row.save() you need a callback to handle errors. Eg. row.save(err=>{
if (err) return console.error(err);
// else all is good
})
Great suggestion. We sometimes leave out error checks for brevity in the videos but I’d definitely suggest adding that one!
Hello!!! Thank you very much for the video. I have a doubt, with the query field how can I get rows where there is an empty field and vice versa.
Cheers for your time my man!
Thanks for watching Doug!
Hi..thanks for this great tuto, i want to add a new column to the sheet from my nodejs code, is there any way to do that please?
Awesome! Is there any way to do this with user selected spreadsheets without the service account?
Why did you select Google Drive API and not Google Sheets API at 0:33?
Amazing... Thanks a lot , but now here trying to figure out how to add at position 2 instead of adding to the end . Any suggestions¿
Thats pretty cool,
Can this package be used to create a cell with a function?
Like =sum() etc...
Thanks a lot. This was really helpful.
Thanks for watching and very glad we could help!
Can you add the URL of the example spreadsheet used in the video? I can't seem to find it with a simple google search.
Could you make a new video on the updated version of spreadsheet?
Thanks for the old version of API(v3), do you think make new video about new version of spreadsheet API (v4)
It helped me! Thank you! so much!!!
How I can add multiple queries? any idea?
Hi thank you for the video ! I'm wonderign how to get a column name that has space in it ?
Thanks a lot for this, worked exactly as explained. You went very quickly so had to pause the video a fair bit but that's still preferable to unedited dev videos.
Trying to strike that balance is very difficult. Focus for future videos is to make sure the pacing is quick enough that it doesn’t drag on but not so fast that you have to pause too too much. Also, more github repos/gists.
@@twilio Nice one! keep it up
i am in computer enginnering as well and i can relate for the need to distinguish it between computer science
I love your tutorial and Ive tried to follow it up. I have error at 4:50 saying that "Error: Cannot find module './credentials.json'". Any way to fix it?
cool Idea, I've been looking for a database solution for sometime now..
expecting more vids like this
Thanks for watching!
At 4:49 I'm getting an error "TypeError: GoogleSpreadsheet is not a constructor". How do I fix it? The code is exactly what is shown.
same error how do we fix this?
same
npm install google-spreadsheet@2.0.6
how to fix ^^
to import google-spreadsheets you need
const { GoogleSpreadsheet } = require('google-spreadsheet');
wow this looks awesome, wondering if I could use a spreadsheet as a database for a ecommerce
There will be potential performance issues with this at scale. I like this for small projects or internal prototyping. It’s also very useful for working with data that stems from collaboration that’s already occurring in Sheets.
Is there a big latency? Like could I update data in the spreadsheet every 100ms or something around that with data from multiple sensors etc.?
what if there was an image uploaded in the cell? how would you do to get the url
Been trying to figure this out for you. I keep getting back an empty result for that field though.
Could I use this same approach to display data from a spreadsheet dynamically on a site?
First Thank You very much for this video.
I have one doubt -> how can I add multiple queries to it?
Thia line inside the console.log appears in monocolor string code inside vsc:
console.log('Title: ${sheet.title}, Rows: ${sheet.rowCount}');
And when run in terminal just showed the string with no data from the vars?
this is a better tutorial than google has
*Excellent Video*
Thanks for watching!
Thank You For Sharing the video but can I apply the same code on Google Documents also(including Google Doc API)??
no
The spreadsheet key is different and shorter if you try to upload xls sample sheet on drive and then open it via Google Spreadsheet .If u use a spreadsheet created by google spreadsheet in the first place the key is different and long .Why is it so ?
Thank you
Hi great video, I used this code to insert data in my google sheet. Is there documentation on how to loop data in the google sheets?
I'm not sure what you mean by loop data. Can you elaborate?
BTW do we need to buy the subscription of google api or is it free?
Hi Twilio, why do we need to use promisfy, async and await? Cause it seems if we always await it will be the same as calling them synchronously?
This is a good question. There are definitely use cases where you won’t always want to await. Some of these operations take a while with bigger data sets. The main reason we’re promisifying this SDK is mostly to modernize it a bit. It is not very fun to work with its callbacks so the promises streamline things a bit.
@@twilio that makes sense. Thanks!
I have triple check I copied your code exactly, but i get "UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block" I do not why :(. Can you help me?
what is the syntax for AND operator in a query?
Thanks a lot for a super tutorial. question: can we do it without sharing to an account? I mean manipulating data with API key only?
I believe I remember reading somewhere that if the access is set to "anyone with a link can edit" it will work but I have not tried it personally.
@@twilio thanks for quick response. :)
Sure thing! Let me know if it works for you.
Thank you for the amazing video. I understand this is a old video but when i'm working on it error pop outs and said googleSpreadsheet is not a constructor is there any way to solve this?
I reverted to version 2.0.6 of the google-spreadsheet npm package as a test, and it worked. Looks like the updated package version broke this functionality
I tried following the readme on the lastest version of the NPM package and that works as well www.npmjs.com/package/google-spreadsheet
@@mikalmorello7768 Ok thank you so much! I'll try it later
@@mikalmorello7768 This worked, thanks!
@@mikalmorello7768 Brother, when I console.log(rows) after await sheet.getRows() it print JSON not OBJECT.... how to fix that?
What about the opposite process? Is it possible to have a Js in Visual Code and collect data sendind this data to a Google Sheet ?
what does the double brackets in line 8 mean??
Hello, please help me to find way out(( how can i return the value(or array of values) with this functions so i can do something with it in my programm, cos it only returns Promisies(
Can you make an updated video on v4 google-sheets?
The query function seems to have been deprecheated with the release of V4, do you know of any other way to only get rows with specific data values?
Hi, were you able to get a solution for this?
@@rakibulhasan8654 Nope, sadly
What if you want to pull/push data across multiple tabs? How would you specify the tab?
Each "tab" is a "sheet" in the spreadsheet. You can get a specific sheet by calling: const sheet = doc.sheetsByIndex[0];
This gets the first sheet. To get the second, specify [1] instead of [0].
Then you would work with that sheet as shown in this example.
What's the name of this theme on your text editor?
It's called Quiet Light. I believe it was built in to VS Code.
hello twillo iam getting an error (node:6212) UnhandledPromiseRejectionWarning: Error: read ECONNRESET
at TLSWrap.onStreamRead (internal/stream_base_commons.js:201:27)
(node:6212) 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:6212) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the
You’ll need to add error handling either with a try-catch or with a catch clause on the promise. This was left out for brevity. If you need more details, check here: javascript.info/promise-error-handling
can i use that on angular ??
Hey, amazing video! For querying rows, I'm not able to pass anything with spaces in it - how do I make it work?
Try %20 instead of space.
@@twilio Tried that already, didn't work :( Instead I just read the entire dataset and then did the querying locally :P
man you are good
Thanks for watching!
If we have json file and bunch of data then how to push all json data to google sheet via nodejs reply soon
I'm trying to use my bot to pull a url from a google spreadsheet and send it in a discord channel, but this url is created through the ImportXML function, and the bot is reading the url as "Loading..." instead. I can see the url perfectly fine on my screen, but apparently the bot can't. Is there any way to solve this problem?
why do you use the google drive api and not the google sheets api? the tutorial i was looking at said to use the google sheet api
Thanks for the tutorial. Not sure why I'm getting row count of 1000, and col count of 26. Any idea what I'm doing wrong? LOL Thanks again!
Is there a possible way to put JavaScript back in a link what is shared. But the owner of the link. Blocked all kind of JavaScripts ?.
Anyone know how to correct this error "TypeError: GoogleSpreadsheet is not a constructor" I have done the exact steps as he has in the video and I run node spreadsheet.js and get this error
how do you make the vsc's cursor transition smoothly?
There’s a setting (something starting with editor.smoothCursor) that makes this happen.
Bro any way to contact you actually I have some questions ?
Awesome !!!
is it possible to have multiple query parameters?
Yep, you can use the query format documented here - developers.google.com/sheets/api/v3/data#send_a_structured_query_for_rows
thank you so much
Perfeito! Muito obrigado
It doesn't work for me. I get this messag, Error: Spreadsheet key not provided. And I followed the instructions exactly. :-(
TypeError: Cannot set property 'jwtClient' of undefined
Hi. i got "Cannot set property 'jwtClient' of undefined" on the " await promisify(doc.useServiceAccountAuth)(creds);" line. Please help
Checkout Stackoverflow discussion
stackoverflow.com/questions/60349027/cannot-set-property-jwtclient-of-undefined-trying-to-use-node-js-with-google
can I use sheets api ?
Hello, I had an error spreadsheets.js:200
throw new Error("Spreadsheet key not provided.");
but i have client_secret.json file and i defined it. Please help me
How to set spreadsheet as public link?
Is it possible change cell formatting? say set font to bold or cell background color...
Unfortunately, the library we used in this video just deals with the data access portion of this. The Google API does support the changes you want to make but you'll have to use the slightly less user-friendly Google SDK or direct API calls to do this (scroll down and there's a JS sample): developers.google.com/sheets/api/guides/conditional-format
@@twilio I was afraid you would say that..
I was afraid I was going to say that too. I used the Google SDK for our PHP video since there wasn’t an active community library there. Not my favorite.
How do I export google sheet data to mongodb?
Now WE can do a reverse shell tcp with node
I would prefer using C# or Java for something like this, specially C#, but I see why you would prefer not to
We have videos on this topic in those languages on this channel as well 😀
Twilio I’m definitely subscribing if that’s true
Google Sheets and .NET Core with C# th-cam.com/video/afTiNU6EoA8/w-d-xo.html
Google Sheets and Java th-cam.com/video/8yJrQk9ShPg/w-d-xo.html
Why are you working with google drive API when the sheet API is the thing you need?
Something isn’t working for me, the console just says “GoogleSpreadsheet is not a constructor” what have I done wrong?
I managed to get it fixed, if you have a similar problem just google the error message and you’ll quickly find a solution
Nice!
Succinct, 👌🏾
[ viewer subscribed 😎 ]
Thanks for watching and for the sub!
All these Functions no longer work. The Library you used got updated. Now when I try it gives Errors.
Sorry, I am a newbie. What is the function of doing this?
Explain more, i don't understand your problem
This allows you to access, store, and modify data in Google Sheets spreadsheets.
@@twilio so it can be like a database?
Hi, thanks for this video. Although I think something has changed in Google-Spreadsheet. I'm using the exact same code like you but getting an error back each time.
This is the code:
const { GoogleSpreadsheet } = require('google-spreadsheet');
const { promisify } = require('util');
const creds = require('./blablabla.json');
async function accessSpreadsheet() {
const doc = new GoogleSpreadsheet('xxxxxxxxxxxxxxxxxxxxxxx')
await promisify(doc.useServiceAccountAuth)(creds)
const info = await promisify(doc.getInfo)();
const sheet = info.worksheets[0];
console.log(`Title: ${sheet.title}, Rows: ${sheet.rowCount}`)
}
accessSpreadsheet();
The error I get back over and over again is:
(node:5132) UnhandledPromiseRejectionWarning: TypeError: Cannot set property 'jwtClient' of undefined
at useServiceAccountAuth (C:\Users\Nils\Desktop
ode
ode_modules\google-spreadsheet\lib\GoogleSpreadsheet.js:53:20)
at internal/util.js:278:30
at new Promise ()
at useServiceAccountAuth (internal/util.js:277:12)
at accessSpreadsheet (C:\Users\Nils\Desktop
ode\cryptopremium\sheet.js:8:47)
at Object. (C:\Users\Nils\Desktop
ode\cryptopremium\sheet.js:14:1)
at Module._compile (internal/modules/cjs/loader.js:956:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:973:10)
at Module.load (internal/modules/cjs/loader.js:812:32)
at Function.Module._load (internal/modules/cjs/loader.js:724:14)
(node:5132) 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:5132) [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.
Anyone has a solution?
Something is not right with your credentials. You can try putting a catch on the promises that fail to see if it will give you more info.
Checkout Stackoverflow discussion
stackoverflow.com/questions/60349027/cannot-set-property-jwtclient-of-undefined-trying-to-use-node-js-with-google
great guide! it worked for me a couple of times
however, when i run:
const GoogleSpreadsheet = require('google-spreadsheet')
const { promisify } = require('util')
const creds = require('../secret.json')
async function access() {
const doc = new GoogleSpreadsheet('1Ra9PoBxbAKFa3V34yl492H86UnOXzfgo_72ya77q3sA')
await promisify(doc.useServiceAccountAuth)(creds)
const info = await promisify(doc.getInfo)
const sheet = info.worksheets[0]
console.log(sheet.title)
}
access()
i get this error:
(node:3956) UnhandledPromiseRejectionWarning: TypeError: Cannot read property '0' of undefined
at access (C:\pathto.js:8:31)
at process._tickCallback (internal/process/next_tick.js:68:7)
(node:3956) 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:3956) [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.
why is info.worksheets undefined when i console.log() it? the id is correct
I'm getting the same issue. I know this question is a while back, but have you found any solution?
@@koriv6329 I haven't found a solution in the v2 api shown in this video, but the new v3 Google-spreadsheet api fixes this
Unfortunately a lot of previous syntax has to change from v2 to v3