Control Google Sheets with Node.js / JavaScript (v4 API)

[@jamesgrimshaw]

Building something at the moment where I need to read from a Google Sheet... I'm back here reminding myself how to do it 😂

[@AxelFerrer-sw4xs]

Hi James, if i update a cell, will it show in edit history of the individual cell? I'm looking for a way to edit a cell without it showing in the edit history.

[@quinojuan2]

🤣

[@ravipatelCTF]

😂😂😂 A little RECURSIVE approach , isn't it. The Tutorial creator himself uses his own tutorial to build something.

[@htmlfivedev]

THANK YOU ... FINALLY, SOMEONE SPEAKING CLEAR ENGLISH HAS MADE A TUTORIAL ON THIS TOPIC THAT TRULY HELPS ... THANK YOU !!

[@sauloferreiradossantos8158]

Thanks a lot man, you saved me here, I was requested to do a simple database using sheets API but the google documentation is awful,your explanation is very good, congrats!

[@jamesgrimshaw]

Appreciate you watching! :)

[@TraumaMedia]

Thank you for making this. My website was using v3 and it just got deprecated this month and I was totally lost with how to read a google sheet with the v4 api. This was a life saver. Thank you!

[@BrandonClapp]

Thanks for sharing. I was thinking about doing this for my mailing list, where spinning up a PostgreSQL database just for this seemed overkill. I didn't expect it to be this involved, so I'm really glad you put this together.

[@a.alharasis]

Thanks for the great explanation, quick note: you don't need to use auth in the options every time you are using googleSheets, because you already pass it in the start (when you set google sheet version and auth)

[@jamesgrimshaw]

Great point :)

[@t-damer]

Oh thank you, man 🙏 I've been trying to do a similar thing for three days, almost gave up and tried googling for such a video, and there you, thanks again 💪

[@LongVuDucIcake]

I tried some tutorial on the internet and fail. Then I find yours :). Thank you so much!

[@mohammadyusuf37]

Thank you for such a great, crisp tutorial. This should be the goto place instead of the docs.

[@dub161]

Thank you, you are a savior. I was given a test related to google sheet connector for my first dev role interview process and successfully completed because of you. Thanks a lot for creating this.

[@alanhunold7546]

I have been looking for a tutorial on how to do this for a couple months. This is the best-explained one I have come across. Thank you! I have now subscribed :)

[@jamesgrimshaw]

Wow thanks so much! More videos coming soon :)

[@generalpompeyo]

Great tutorial, thanks ! Worth mentioning : googleapis ONLY works on the server side. This will not work in a client side application like React or Vue alone.

[@kamenriderinfinity]

bruh... literally what I was looking for. Thank you for making this complicated subject easy to understand.

[@my_codingchannel7479]

You made this and the website it is about and haven't made a video since 😞...... you are a good teacher...

[@mehrosenasir9974]

Clean, Clear, and Concise. Bravo!

[@siriusFish1]

Thanks for this! I was bouncing around reading the docs for way too long!

[@fabricio_zm]

This was exactly what I needed Lol Thank you so much!! Great video!!

[@jamesgrimshaw]

So glad you found it useful! :)

[@iBen-ry6pj]

Quite a handful of instructions. I think is a lot easier to just create a google app script to handle the CRUD operations on google sheets. That way you only need the deployment url in your nodejs to initiate reads and writes. Thanks though, it was very informative!

[@saulhiram2145]

Good video, I was reading the Google Sheet API and I don't undertand but I can

[@avalancherelapse]

8:15 when i do "nodemon index.js" it tells me that "nodemon" is an unknown command, even though i installed the package. what's going on?

[@zyada9334]

Thank you so much for this tutorial. it's perfect for me because I'm working with express and google sheets API.

[@re_allyedge]

Awesome video! Sad to see that you stopped making more videos.

[@zeeu]

best explanation I've ever seen

[@MarcoS-bx5uk]

This is great, James, it served me perfectly. Thank you.

[@vietcuongquach636]

u save my day!! that a really wonderful tutorial

[@corychecketts]

You are awesome. This was just what I was looking for!

[@rizzbod]

Great tutorial on working with Google sheet api , that is get starter for everyone. Thnx

[@dejo095]

One tip for you, since you share tips with us :-) npm init -y will initialize project folder with default values for you

[@jamesgrimshaw]

Great tip!

[@LiquidMasti]

great video and content. love the teaching style keep the good work up man.

[@segunthemarketer2195]

This is a very very beautiful tutorial! Very detailed… 🙏🏽

[@keithpowell6564]

Thanks for the tutorial, James. There isn't a lot of solid tutorial content surrounding the Sheets API and Node.js but yours is spot on. Do you have the link to your blog in which you take a deeper dive into working with Google Sheets API? Also - ever think about teaching a Udemy course on the subject?

[@jamesgrimshaw]

I never got around to writing a blog post on this! I want to make more videos and maybe courses in the future though

[@NeloReis71]

thank you. there should be a free no-code app to recreate this particular scenario. Custo form submitted to google sheets and read from google sheets a render as HTML.. this would cover 90% of every small business needs.

[@toffotin]

Thanks a lot! This seems like exactly what I've been trying to make. Just trying to figure out how to make it work on a website next. I guess at least I have to secure the credentials file somehow...

[@pjc825]

Great video, thanks for making this so simp😀le compared to most other videos available on KZbin 👍

[@toyinatofarati5512]

Thank you for this great content. Unfortunately I'm experiencing difficulties implementing this. I have a basic form set up with react and nodemailer which works fine but sending the data to Google sheets returned an error: invalid credentials. Pls how do I fix this

[@garikmelqonyan9130]

Thanks for this great tutorial, James.

[@maratiot]

That’s a great tutorial regarding Google Sheets API. Your explanation is decent and very detailed. Could you make a video how to sync Google sheets with Next.js?

[@SuboptimalEng]

Neat idea! Imma take some notes 📖

[@jamesgrimshaw]

So cool isn’t it!!

[@samandaryusupov6134]

THANK YOU VEEEERRRRYYY MUCH. you are the best. i am so glad i found this vid

[@vipulpawaskar]

Dotenv module will be helpful to hide credentials details. Very Helpful... Thanks for sharing

[@melihkarakaya79]

how to do that?

[@osherezra8460]

Just found out the channel, thank you keep it brother great content and flow cheers

[@techsamar]

Beautifully Explained!! Thanks

[@volatilnost]

thank you! Amasing tutorial!

[@akritworanithiphong]

Thank you, the tutorial is nicely done. :D

[@patrickudeh5869]

Simple to the point... Do you have any tutorial on google fit api?...

[@wibu8757]

This video is exactly what I needed, thaks )

[@mfarhanarif7476]

Thanks Sir, Really helpful and the way explained is awsome.

[@jean-louisgouwy]

You helped me a lot. Thx Little question, I just created a new google spread sheet. I received my 200, but it seems that only the "account service" from the api key has an access to it. Which is weird because my main account is responsible of the account service. Any idea ?

[@jean-louisgouwy]

For those who wonder the same question : you need to use the GDrive Api. After creating the file with account service, you need to use : developers.google.com/drive/api/v3/reference/permissions/create ``` const result = await googleSheets.spreadsheets.create(params); const fileId = result.data.spreadsheetId; drive = google.drive({ version: "v3", auth }); const res = await drive.permissions.create({ resource: { type: "user", //role: "owner", role: "writer", emailAddress: "xxxx" // Set the email address you want to give the permission. }, fileId: fileId, fields: "id", //transferOwnership: true, }); ``` Still have an issue with ownership that i need to fix with the above code : Consent is required to transfer ownership of a file to another user.

[@landenbrewer7319]

This actually saved me. Thanks bor.

[@dev_danny]

Great explanation, pretty much got all i need to know. Thanks :)

[@smilyking6595]

First of all, thank you for your video, it helped me alot! Sadly I have an issue with my authentification. When I try to start the server/ refresh the server at the point of minute 13 and reload the Website in my browser, the site can not load. In VS Code I get an error. message: ‘Invalid Credentials’, domain: ‘global’, reason: ‘authError’, location: ‘Authorization’, locationType: ‘header’ Unfortunately I have no clue where and what went wrong. My Code looks identical. Maybe you or some else can help me out. Thank you in advance! And greetings from germany Malte

[@hugopachy2036]

Hey man! Insane tutorial thanks you ! Same a lot of time

[@HagbardCelineOnRust]

gj creating a video on this. you saved me some time. ty sir

[@jamesgrimshaw]

Appreciate you watching! :)

[@JuanCastro-uf6er]

thnks a lot, this video is really helpful.

[@rogue1183]

Wow, thanks for the tutorial!

[@cloudagnostic]

Thank you! Very helpful and informative.

[@beccaadolph]

Thank you. Wish you would have explained Express

[@premkumar01]

Thanks for the video @James Can I get the details about integrating the same from react ?

[@aryupanchal]

please make something about passport js ur explanation is really top notch

[@jamesgrimshaw]

The next series will be an authentication series!

[@drvision-xz7ui]

Best video. Working very well for me

[@dasdunetechnologies1695]

Another question about namedrange, they are listed in your Metadata variable but how to create a new one from the API ?

[@Jurinza55]

producer's version? I just want to make sure I get the right one for .

[@Jonas-sg1eo]

I only have ones question how to install googleapis on server ? is there are import link or something to import on index.html ?

[@pavelvisek4374]

8:19 I have this error: nodemon : The term 'nodemon' is not recognized as t he name of a cmdlet, function, script file, or oper able program. Check the spelling of the name, or if a path was included, verify that the path is corre ct and try again. At line:1 char:1 + nodemon index.js + ~~~~~~~ + CategoryInfo : ObjectNotFound: (nod emon:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundExcep tion please help

[@jamesgrimshaw]

You don’t have nodemon installed - recheck the part where we npm install various packages

[@pavelvisek4374]

@@jamesgrimshaw ok, but i type in terminal npm install -D nodemon and it saw it is installed, but is a nodemon really needed? I haven't watched whole video yet xd

[@jamesgrimshaw]

@@pavelvisek4374 Nope you don’t need it, just makes development easier because you don’t have to stop the server and restart it again every time you make changes

[@pavelvisek4374]

@@jamesgrimshaw do you have discord? I would really appreciate it if I could learn something or ask something without having to write in the comments

[@jamesgrimshaw]

@@pavelvisek4374 sure my discord is jamesgrimshaw#2024

[@primebreaker4839]

The metaData variable isn't working for me. I think it is to do with the spreadsheet Id but I have already given the bot access to the spreadsheet so I am not sure what's up with it

[@saikrishnavs7886]

hey james...Thanks for the video but what if iam creating the spread sheet through a api call and i need to grand the permission for the service account. For me it is not possible to share the permission manually cause im creating the spread sheet through api call itself

[@basparshirkooh976]

Perfect, you saved me. THANK YOU

[@adityavartak007]

Cant get the metadata, the keyFile parameter in my client object is undefined?

[@BusterBrown1217]

How would I go about deleting the second row and moving every row below it up? Is that feasible?

[@eltonleao3979]

Thanks! Awesome explanation!

[@astrotecn]

Is there any way to read from a spreadsheet and use the data on the spreadsheet to input information on fields on a website to automate boring work?

[@developeraccount438]

can be all new column and update the entere value later?

[@Jonas-sg1eo]

when i push this to my browser/client-side i get error "require is not defined"

[@aronrestyramillano1321]

Hello! So I am trying to make it so that this specific index.js file that handles the sending of data to the google sheets, is called by another normal .js file question is, how I will pass data to it and have it get triggered by a specific event, to go an append the data, to my google sheets? without using the .ejs button that you have created

[@smileguptaaa]

How can we add background color to the sheet cell?

[@syedaffan2104]

@James I want to append a json object with it's values to google sheet using append. How can I do that? The rest is working fine. The only issue is I want to pass the json values be pasted in seperate columns, right now whole json object is being written to only one row as a whole.

[@rohitraj2321]

hey mate... i was trying to create an app to allow users to edit their sheets itslef. how can i do that?

[@luisforni86]

Hello, how can you make a FOR loop with 2 columns, for example phone number and message to send?

[@nemethricsi]

Great tutorial, thank you! I’m wondering how to handle credentials.json? I guess you won’t commit it to the git so how would you provide this file for a deployment eg Heroku?

[@JumpingDaNieL1]

Nice Video! Please tell me, how i can read the values in sheets and use with aplications to for example show in div?

[@MinhThongLe]

I wonder when you deploy your app. So other users can have the right to write into google sheet ?. Thanks

[@streamcalter442]

Bro I am Missing your videos, I subscribed you with hope

[@koteshmeesala1630]

How did you prettify the json in the browser page

[@Angelusundertaker666]

Was hoping this would be on the google-spreadsheet npm module, but to achieve your original goal you could have created a Google form and have the results appear in a sheet.

[@jamesgrimshaw]

Very true, but I wanted to mess around with the API directly for more complex use cases in the future. Form was just a good starter example

[@jacek0720]

Thank You! Great job!

[@Christopher-ew7jw]

How do you use a keyFile with Netlify without including your keyFile in your version control?

[@ИкболжонИсаков-п7д]

Very good tutorial! Thank you.

[@diggitydingdong]

This is too much power... really well done video! No complaints here, everything worked as you described, but maybe if you elaborated on more than just appending / reading, that would've been nice.

[@jamesgrimshaw]

Thanks so much for the kind words! In hindsight, I wish I had spent more time on the actual Google Sheets API rather than the demo application - I wanted to keep the video as short as possible, so I only demonstrated the features I actually found useful in the API. I am going to write a blog post on this soon, and will make sure it demonstrates more capabilities of the API!

[@toshunigam8603]

could anyone help why i am getting this response. "data": { "error": { "code": 404, "message": "Requested entity was not found.", "errors": [ { "message": "Requested entity was not found.", "domain": "global", "reason": "notFound" } ], "status": "NOT_FOUND" } } i wand to read the sheet

[@DionnyPrensa]

Awesome. Thanks a lot. I have a question. If I have to update a specific row, would I have to read the entire sheet to then know the position of the row that I have to update? or is there some other way?

[@jamesgrimshaw]

Well in order to update a specific row, you will need to know the row number, which you can then specify as part of the range, using the "update" function rather than append, which works in the same way but the function is called update rather than append. For example, if the row you want to update is row 7, you would use append like in the video but set the range as "Sheet1!A7:B7", or whatever column range you want to use (B7:C7 or A7:D7, etc.) Hope this helps, thanks for watching!

[@oqykz]

рахмет, Жеміс

[@thekevalpanchal]

Can we upload files on Google sheet from registation form when user upload it

[@dasdunetechnologies1695]

Very good video clear and sharp! A little question though , any idea how to delete a row with the API ? Thanks in advance.

[@varma8669]

I really like your vscode setup. How did you get the arrow shape on your terminal ?

[@jamesgrimshaw]

My terminal is like that because of something called ohmyzsh. I think the plugin was called Powerlevel10k

[@misterl8129]

Hi! Great video. It worked for me. I have a question tho. The read of the csv WORKS just if I have a csv with just 1 sheet. I have another with 3 sheets and when i Try to get the values says errors: [ { message: 'This operation is not supported for this document', domain: 'global', reason: 'failedPrecondition' } ] what could it be?

[@misterl8129]

Nevermind. I diplucated the file, get that id and work. Weird tho

[@phutruong9687]

thank you so much. it's very helpful

[@LucXX36]

This is so great. Thank you. As long as I know adding Reactjs to this should not be a big deal isn't it?

[@jamesgrimshaw]

Convert the node.js app to an API, and then use fetch calls to integrate with a React.js app. Should be straightforward :)

[@kevenmendes3670]

When I run i in my local machine it's working well, but i sent it to git hub and published with github pages. But it isn't working online, I don't know why. The pages redirect to an error: 405 Not Allowed And I receive a console error that says. POST ... 405

[@melihkarakaya79]

Thanks for the tutorial! How should we hide the credentials while also serving them to the nodejs file?

[@developerGRohit]

by using .env kzbin.info/www/bejne/a4ipqpupo7pnf80&ab_channel=SteveGriffith-Prof3ssorSt3v3

[@miraclehesed8959]

Hi, thanks for the solution at hand but i have a problem. When I console.log the metaData.data, the object "sheets" returns this sheets: [ { properties: [Object] } ]. What seems to be the problem.

[@miraclehesed8959]

Okay it's resolved.