Posted on • Edited on • Originally published attravislramos.com
Automate Your Google Sheets Work
Lets Begin
Many people and business's use Google Sheets to manage important data. Whether it be inventory count, expense history, or a list of clients, google sheets is a powerful tool which can be used to store this type of data and so much more!
Gone are the days of spending countless hours manually inserting your precious data. With Google's API you can automate certain tasks that would otherwise cost you time and money.
In this article I will teach you 3 things:
- How to Create a new sheet
- How to Write to a sheet
- How to Read from a sheet
Before we begin you need to enable the Google Sheets API. Go into your developer console, click "library" from the menu, and search for the Google Sheets API. Click on it and hit "enable". This will give you your "Client Configurations" which will give your script or application the ability to use the API with your google account. Download these configurations and save the "credentials.json" file to your working directory.
After this you will need to run the following NPM command to install the libraries:
'npm install googleapis@39 --save'
The first step before each script is generating authentication which is dependent on the Scope you are allowing your script to have. We will be giving our script the ability to create, write, and read from a sheet by setting our scope to 'https://www.googleapis.com/auth/spreadsheets'.
The authentication at the beginning of each script will look for a file called 'token.json' to authorize your script. If the file doesn't exhist it will prompt you in the console to authorize your scipt and create the file. Now it is time to begin!
Create A New Sheet
constfs=require('fs');const{google}=require('googleapis');constreadline=require('readline-promise').default;constSCOPES=['https://www.googleapis.com/auth/spreadsheets'];constTOKEN_PATH='token.json';//authorizationasyncfunctionauthorize(credentials){const{client_secret,client_id,redirect_uris}=credentials.installed;constoAuth2Client=newgoogle.auth.OAuth2(client_id,client_secret,redirect_uris[0]);try{consttoken=fs.readFileSync(TOKEN_PATH);oAuth2Client.setCredentials(JSON.parse(token));returnoAuth2Client;}catch(err){constauthorizedClient=awaitgetNewToken(oAuth2Client);returnauthorizedClient;}};asyncfunctiongetNewToken(oAuth2Client){constauthUrl=oAuth2Client.generateAuthUrl({access_type:'offline',scope:SCOPES,});console.log('Authorize this app by visiting this url:',authUrl);constrl=readline.createInterface({input:process.stdin,output:process.stdout,});constcode=awaitrl.questionAsync('Enter the code from that page here:');rl.close();const{tokens}=awaitoAuth2Client.getToken(code);oAuth2Client.setCredentials(tokens);fs.writeFileSync(TOKEN_PATH,JSON.stringify(tokens));console.log('Token stored to',TOKEN_PATH);returnoAuth2Client;};asyncfunctioncreateSpreadsheet(sheets,title){constresource={properties:{title,}}const{data}=awaitsheets.spreadsheets.create({resource});console.log(`Created new spreadsheet with ID:${data.spreadsheetId}`);returndata.spreadsheetId;}constmain=async()=>{constcontent=fs.readFileSync('credentials.json');constauth=awaitauthorize(JSON.parse(content));constsheets=google.sheets({version:'v4',auth});consttitle="test";awaitcreateSpreadsheet(sheets,title);};main();This will create a new spreadsheet and give you an ID to associate it with.
Write to A Sheet
Use the sheetId from the previous script for the script below.
constfs=require('fs');const{google}=require('googleapis');constreadline=require('readline-promise').default;constSCOPES=['https://www.googleapis.com/auth/spreadsheets'];constTOKEN_PATH='token.json';//authorizationasyncfunctionauthorize(credentials){const{client_secret,client_id,redirect_uris}=credentials.installed;constoAuth2Client=newgoogle.auth.OAuth2(client_id,client_secret,redirect_uris[0]);try{consttoken=fs.readFileSync(TOKEN_PATH);oAuth2Client.setCredentials(JSON.parse(token));returnoAuth2Client;}catch(err){constauthorizedClient=awaitgetNewToken(oAuth2Client);returnauthorizedClient;}};asyncfunctiongetNewToken(oAuth2Client){constauthUrl=oAuth2Client.generateAuthUrl({access_type:'offline',scope:SCOPES,});console.log('Authorize this app by visiting this url:',authUrl);constrl=readline.createInterface({input:process.stdin,output:process.stdout,});constcode=awaitrl.questionAsync('Enter the code from that page here:');rl.close();const{tokens}=awaitoAuth2Client.getToken(code);oAuth2Client.setCredentials(tokens);fs.writeFileSync(TOKEN_PATH,JSON.stringify(tokens));console.log('Token stored to',TOKEN_PATH);returnoAuth2Client;};asyncfunctionwriteToSheet(sheets,spreadsheetId){constvalues=[['Name','Meals A Day','Body Fat Percentage']];constresource={values,};constrange='A1:C1';constvalueInputOption='USER_ENTERED';const{data}=awaitsheets.spreadsheets.values.update({spreadsheetId,range,resource,valueInputOption})console.log('Updated cells:'+data.updatedCells);}constmain=async()=>{constcontent=fs.readFileSync('credentials.json');constauth=awaitauthorize(JSON.parse(content));constsheets=google.sheets({version:'v4',auth});constsheetId="1UXvLBLbKSUlyQV3A3m-r-lmBoxN2TEUzH6USfc6XF6g";awaitwriteToSheet(sheets,sheetId);};main();Read From A Sheet
constfs=require('fs');constreadline=require('readline-promise').default;const{google}=require('googleapis');constSCOPES=['https://www.googleapis.com/auth/spreadsheets'];constTOKEN_PATH='token.json';//authorizationasyncfunctionauthorize(credentials){const{client_secret,client_id,redirect_uris}=credentials.installed;constoAuth2Client=newgoogle.auth.OAuth2(client_id,client_secret,redirect_uris[0]);try{consttoken=fs.readFileSync(TOKEN_PATH);oAuth2Client.setCredentials(JSON.parse(token));returnoAuth2Client;}catch(err){constauthorizedClient=awaitgetNewToken(oAuth2Client);returnauthorizedClient;}};asyncfunctiongetNewToken(oAuth2Client){constauthUrl=oAuth2Client.generateAuthUrl({access_type:'offline',scope:SCOPES,});console.log('Authorize this app by visiting this url:',authUrl);constrl=readline.createInterface({input:process.stdin,output:process.stdout,});constcode=awaitrl.questionAsync('Enter the code form that page here:');rl.close();const{tokens}=awaitoAuth2Client.getToken(code);oAuth2Client.setCredentials(tokens);fs.writeFileSync(TOKEN_PATH,JSON.stringify(tokens));console.log('Token stored to',TOKEN_PATH);returnoAuth2Client;};asyncfunctionreadSheet(sheets){constranges=['Sheet1!A2:B'];const{data}=awaitsheets.spreadsheets.values.batchGet({spreadsheetId:'1UXvLBLbKSUlyQV3A3m-r-lmBoxN2TEUzH6USfc6XF6g',ranges,});console.log(data.valueRanges[0].values);};constmain=async()=>{constcontent=fs.readFileSync('credentials.json');constauth=awaitauthorize(JSON.parse(content));constsheets=google.sheets({version:'v4',auth});awaitreadSheet(sheets)};main();You should see the results displayed in your console from when you wrote to your spreadsheet.
And Thats It!
If you found this article helpful,subscribe to my newsletter where I'll be sending more content like this directly to your inbox on a weekly basis!
Top comments(0)
For further actions, you may consider blocking this person and/orreporting abuse
