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

In this tutorial, we use the Google Sheets API v4 with Node.js & Express to completely control Google Sheets (read & write) and at the end create a demo video request application! This API can help you create anything from a totally custom "Google Forms" replica, all the way to using Google Sheets as your database solution! I hope you enjoy and find this tutorial useful!
----------------------------------------------------------
👀 Tutorial Links:
----------------------------------------------------------
GitHub Repository: github.com/jrgrimshaw/google-...
Google API Console: console.cloud.google.com
----------------------------------------------------------
💻 Commands Used:
----------------------------------------------------------
npm init
npm install express ejs googleapis
npm install -D nodemon
nodemon index.js
----------------------------------------------------------
⏰ Timestamps:
----------------------------------------------------------
0:00 Introduction
0:30 Get Google API Credentials
4:39 Setup Node.js/Express Application
8:28 Use the Google Sheets API!
20:19 Optional Extra: Create a Video Requester Demo Application!
25:39 Outro
----------------------------------------------------------
📚 Resources
----------------------------------------------------------
Editor (Visual Studio Code): code.visualstudio.com/ Theme: Synthwave '84
Web Browser (Back on Chrome!): www.google.com/intl/en_uk/chr...
Terminal (iTerm, Mac only): iterm2.com/
MDN (where I reference JavaScript documentation): developer.mozilla.org/en-US/d...
----------------------------------------------------------
🔗 My Links
----------------------------------------------------------
GitHub: github.com/jrgrimshaw/
Instagram: / jrgrimshaw
LinkedIn: / jrgrimshaw
Personal site: jgrimshaw.com
#jamesgrimshaw #nodejs #googlesheets

[@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]

🤣

[@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.

[@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!

[@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 💪

[@htmlfivedev]

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

[@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.

[@mohammadyusuf37]

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

[@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 :)

[@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! :)

[@kamenriderinfinity]

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

[@siriusFish1]

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

[@LongVuDucIcake]

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

[@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.

[@mehrosenasir9974]

Clean, Clear, and Concise. Bravo!

[@fabricio_zm]

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

[@jamesgrimshaw]

So glad you found it useful! :)

[@zyada9334]

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

[@abdulrhmanalharasis4119]

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 :)

[@re_allyedge]

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

[@segunthemarketer2195]

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

[@MarcoS-bx5uk]

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

[@rizzbod]

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

[@corychecketts]

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

[@my_codingchannel7479]

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

[@LiquidMasti]

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

[@vietcuongquach636]

u save my day!! that a really wonderful tutorial

[@saulhiram2145]

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

[@garikmelqonyan9130]

Thanks for this great tutorial, James.

[@pitchyq2804]

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

[@mfarhanarif7476]

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

[@osherezra8460]

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

[@samandaryusupov6134]

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

[@akritworanithiphong]

Thank you, the tutorial is nicely done. :D

[@techsamar]

Beautifully Explained!! Thanks

[@dasdunetechnologies1695]

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

[@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?

[@cloudagnostic]

Thank you! Very helpful and informative.

[@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

[@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...

[@wibu8757]

This video is exactly what I needed, thaks )

[@paulclarke4099]

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

[@patrickudeh5869]

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

[@rogue1183]

Wow, thanks for the tutorial!

[@hugopachy2036]

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

[@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!

[@JuanCastro-uf6er]

thnks a lot, this video is really helpful.

[@benny.6588]

Thanks brother, this was useful for me.

[@user-qh4ht1yt5j]

Very good tutorial! Thank you.

[@eltonleao3979]

Thanks! Awesome explanation!

[@zeeu]

best explanation I've ever seen

[@jmpsoliz]

Thanks a lot! Great video

[@SuboptimalEng]

Neat idea! Imma take some notes 📖

[@jamesgrimshaw]

So cool isn’t it!!

[@phutruong9687]

thank you so much. it's very helpful

[@jacek0720]

Thank You! Great job!

[@HagbardCelineOnRust]

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

[@jamesgrimshaw]

Appreciate you watching! :)

[@basparshirkooh976]

Perfect, you saved me. THANK YOU

[@landenbrewer7319]

This actually saved me. Thanks bor.

[@user-gu5ts5nx8r]

Very helpful, thank you.

[@giwrgosiwannidis4684]

Great tutorial!

[@drvision-xz7ui]

Best video. Working very well for me

[@juanjoroba]

Thank you so much, idol!!

[@user-eu3ok8dc8b]

great video.thanks!!

[@noeliapardo1605]

good job male, thanks!

[@vazsa7387]

this was so useful thanks

[@vipulpawaskar]

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

[@melihkarakaya79]

how to do that?

[@amthebeast-lolsupportplays599]

Thank you for this video

[@DivitKalem]

Amazing. Thanks. 👏👏

[@beccaadolph]

Thank you. Wish you would have explained Express

[@blink11101]

definitely a good video, thanks for sharing

[@jamesgrimshaw]

Really appreciate you watching! :-)

[@streamcalter442]

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

[@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

[@omarruder4129]

Real nice and concise tutorial! Thanks man.
I was wondering however; how it is possible to make this more modular and make the spreadsheetId dependant on the user input - which ofcourse brings us to the following point; having the right read/write permissions on the spreadsheet.

[@whatyoumissed9994]

yes check alternative video from neuralnine but in python

[@MarsFrom]

Thanks buddy!

[@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.

[@Dtomper]

Thank you so much bro

[@matheuseli165]

great video, thanks

[@myworldbox]

epic tutorials bro

[@premkumar01]

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

[@chirpieful]

Great video!

[@jamesgrimshaw]

Thank you! :-)

[@StratexMedia]

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

[@jamesgrimshaw]

The next series will be an authentication series!

[@josebergues]

Great Video!

[@jamesgrimshaw]

Appreciate you watching :)

[@JumpingDaNieL1]

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

[@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!

[@brianyasierjacobofuentes1294]

fantastic !

[@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 :)

[@afp2003d]

helpful.. thanks

[@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?

[@leonardomauricio3149]

Thanks!

[@leandromendoza9216]

Thank you !!

[@tonmoitanvi1773]

Hey James, you always try to provide something useful. it's an awesome video. since I am using an API tool of Sheet Best which is easy and secure for me, I can't apply something new.

[@altamirsantos6030]

Thanks guy

[@itmoroz]

thank you very much !!!

[@dasdunetechnologies1695]

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

[@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!

[@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.

[@pomarpeggio]

Thank you sir

[@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

[@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?

[@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

[@GeorgeGeorge-ve6il]

Hi James,
This was really helpful. there is such limited resources for the Google Sheet Integration and most of time I get a road block after following some.
but with this tutorial, you were able to explain things in a easy to understand manner.
Could you do a CRUD Html table example for this?

[@teddyheo5386]

yes we need this

[@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