First of all, a brief overview of our use case. Let's say I have a spreadsheet on Google Sheets which is not public and I want to be able to read/modify programmatically through some batch process running on my local machine or some server. This is something I had to do recently with a Node.js application and I found the authentication part a bit tricky to understand. So I thought of sharing my solution and I hope it helps someone in need. There might be better ways of doing this but I am sharing what worked best for me. Since there is no user interaction involved in our use case, we don't want to use the OAuth process where user needs to open a browser and sign in to their Google account to authorize the application. For scenarios like this, Google has a concept of . A service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs. service account Just like a normal account, a service account also has a email address (although it doesn't have an actual mailbox and you cannot send emails to a service account email). And just like you can share a google sheet with a user using their email address, you can share a google sheet with a service account as well using their email address. And this is exactly what we are going to do in this tutorial. We will create a spreadsheet on Google Sheets using a regular user, share it with a service account (that we will create) and use the credentials of the service account in our Node.js script to read and modify that sheet. Prerequisites This tutorial assumes that you have: Experience working with Node.js A Google account A project setup on Google developers console where you have admin privileges Steps Overview Here is the list of steps we will be following through this tutorial: Create a spreadsheet on Google sheets Enable Google Sheets API in our project on Google developers console Create a service account Share the spreadsheet created in step 1 with the service account created in step 3 Write a Node.js service to access the google sheets created in step 1 using the service account credentials Test our service written in step 5 Now that we have an outline of what all we are going to do, let's get started Step 1: Create a spreadsheet on Google Sheets This one doesn't really need any instructions. You just need to login to your google account, open Google Drive and create a new Google Sheet. You can put some random data in it. One thing that we need to take note of is the sheet's id. When you have the sheet open in your browser, the URL will look something like this: . https://docs.google.com/spreadsheets/d/1-XXXXXXXXXXXXXXXXXXXSgGTwY/edit#gid=0 And in this URL, is the spreadsheet's id and it will be different for each spreadsheet. Take a note of it because we will need this in our Node.js script to access this spreadsheet. For this tutorial, here is the data we have stored in our spreadsheet: 1-XXXXXXXXXXXXXXXXXXXSgGTwY Step 2: Enable Google Sheets API in our project on Google developers console We need to enable Google Sheets API for our project in order to be able to use it. This tutorial assumes that you already have a project in Google developers console so if you don't have one, you can create a new one very easily. Once you have the project on Google developers console, open project dashboard. There you should see a button . Enable APIs and Services Click on it and search for Google sheets API using the search bar. Once you see it, click on it and then click on Enable Step 3: Create a Service Account Once you enable Google Sheets API in your project, you will see the page where you can configure the settings for this API. Click on tab on the left sidebar. Here you will see a list of OAuth client IDs and service accounts. By default there should be none. Credentials Click on button at the top and select option Create Credentials Service Account Enter the name and description of the service account and click button. Create Click on the next dialog Continue On the next dialog, you get an option to create a key. This is an important step. Click on the button and choose as the format. This will ask you to download the JSON file to your local machine. Create Key JSON For this tutorial, I have renamed the file and saved it as on my local machine. service_account_credentials.json Keep it somewhere safe. This key file contains the credentials of the service account that we need in our Node.js script to access our spreadsheet from Google Sheets. Once you've followed all of these steps, you should see the newly created service account on the credentials page Take a note of the email address of the service account. We will need to share our spreadsheet with this account. Step 4: Share the spreadsheet created in step 1 with the service account created in step 3 Now that we have a service account, we need to share our spreadsheet with it. It's just like sharing a spreadsheet with any normal user account. Open the spreadsheet in your browser and click on the button on top right corner. That will open a modal where you need to enter the email address of the service account. Uncheck the checkbox for since this will send an email and since service account does not have any mailbox, it will give you a mail delivery failure notification. Share Notify people Click _OK_ button to share the spreadsheet with the service account. This completes all the configuration steps. Now we can get to the fun part :-) Step 5: Write a Node.js service to access the google sheet using the service account credentials We will create our script as a service that can be used as a part of a bigger project. We will call it . It will expose following APIs: googleSheetsService.js getAuthToken getSpreadSheet getSpreadSheetValues The function is where we will handle the authentication and it will return a token. Then we will be using that token and pass it on to other methods. getAuthToken We will not be covering writing data to the spreadsheet but once you get the basic idea of how to use the API, it will be easy to extend the service to add more and more functions supported by the Google Sheets API. We will be using the npm module. So, let's get started by creating a directory for this demo project. Let's call it . googleapis google-sheets-demo google-sheets-demo google-sheets-demo cd $HOME mkdir cd Copy the file that we created in step 3 to this directory ( ). And create our new file . service_account_credentials.json google-sheets-demo googleSheetsService.js Paste the following lines to the file: { google } = ( ) SCOPES = [ ] { auth = google.auth.GoogleAuth({ : SCOPES
  }); authToken = auth.getClient(); authToken;
} .exports = {
  getAuthToken,
} // googleSheetsService.js const require 'googleapis' const 'https://www.googleapis.com/auth/spreadsheets' async ( ) function getAuthToken const new scopes const await return module For now our service has only one function that returns the auth token. We will add another function soon. First let us see what our function does. getSpreadSheet First, we require the npm module. Then we define . When we create an auth token using google APIs, there is a concept of scopes which determines the level of access our client has. googleapis SCOPES For reading and editing spreadsheets, we need access to the scope . https://www.googleapis.com/auth/spreadsheets Similarly, if we only had to give readonly access to spreadsheets, we would have used scope . https://www.googleapis.com/auth/spreadsheets.readonly Inside the function, we are calling the constructor passing in the scopes in the arguments object. getAuthToken new google.auth.GoogleAuth This function expects two environment variables to be available, which is the project ID of your Google developer console project and which denotes the path of the file containing the credentials of the service account. GCLOUD_PROJECT GOOGLE_APPLICATION_CREDENTIALS We will need to set these environment variables from the command line. To get the project ID, you can get it from the url of the project when you open it in your web browser. It should look like this https://console.cloud.google.com/home/dashboard?project={project ID} And must contain the path of the file. So, go to the terminal and from the directory, run the following commands to set these environment variables: GOOGLE_APPLICATION_CREDENTIALS service_account_credentials.json google-sheets-demo GCLOUD_PROJECT={project ID of your google project} =./service_account_credentials.json export export GOOGLE_APPLICATION_CREDENTIALS You need to make sure that you have the credentials file copied in the current directory. Now we will add two more functions to our service: getSpreadSheet getSpreadSheetValues The first one will return metadata about the spreadsheet while the second one will return the data inside the spreadsheet. Our modified file should look like this: googleSheetsService.js { google } = ( ); sheets = google.sheets( ); SCOPES = [ ]; { auth = google.auth.GoogleAuth({ : SCOPES
  }); authToken = auth.getClient(); authToken;
} { res = sheets.spreadsheets.get({
    spreadsheetId,
    auth,
  }); res;
} { res = sheets.spreadsheets.values.get({
    spreadsheetId,
    auth, : sheetName
  }); res;
} .exports = {
  getAuthToken,
  getSpreadSheet,
  getSpreadSheetValues
} // googleSheetsService.js const require 'googleapis' const 'v4' const 'https://www.googleapis.com/auth/spreadsheets' async ( ) function getAuthToken const new scopes const await return async ( ) function getSpreadSheet {spreadsheetId, auth} const await return async ( ) function getSpreadSheetValues {spreadsheetId, auth, sheetName} const await range return module At the top we have added a line sheets = google.sheets( ); const 'v4' This is to use the sheets API. Then we have added the two new functions and . To see all the supported API endpoints for Google Sheets API, check this link getSpreadSheet getSpreadSheetValues https://developers.google.com/sheets/api/reference/rest For our demo, we are only using two of those. The function expects token and the as its parameters. And the expects one additional parameter that is the from which to fetch the data. getSpreadSheet auth spreadsheetId getSpreadSheetValues sheetName By default, a spreadsheet only contains a single sheet and it is named as . Finally we export the newly added functions via . Sheet1 module.exports This completes our . If you need to support more API functions, you can check the reference using the link above, add the corresponding wrapper functions in this service and export it using . googleSheetsService module.exports For any consumer of this service, they will first need to call the function to get the auth token and then pass on that token to the subsequent functions like , , etc. getAuthToken() getSpreadSheet() getSpreadSheetValues() Now that we have our service ready, we just need to test it to make sure it is working fine Step 6: Test our service So we have our service ready. But does it work? Let's check that out. While typically, we would use a testing framework to run unit tests, to keep this tutorial simple, we are going to write a simple Node.js script. From our project's directory, create a new file called and copy paste the   following contents: test.js {
  getAuthToken,
  getSpreadSheet,
  getSpreadSheetValues
} = ( ); spreadsheetId = process.argv[ ]; sheetName = process.argv[ ]; { { auth = getAuthToken(); response = getSpreadSheet({
      spreadsheetId,
      auth
    }) .log( , .stringify(response.data, , ));
  } (error) { .log(error.message, error.stack);
  }
} { { auth = getAuthToken(); response = getSpreadSheetValues({
      spreadsheetId,
      sheetName,
      auth
    }) .log( , .stringify(response.data, , ));
  } (error) { .log(error.message, error.stack);
  }
} {
  testGetSpreadSheet();
  testGetSpreadSheetValues();
}

main() const require './googleSheetsService.js' const 2 const 3 async ( ) function testGetSpreadSheet try const await const await console 'output for getSpreadSheet' JSON null 2 catch console async ( ) function testGetSpreadSheetValues try const await const await console 'output for getSpreadSheetValues' JSON null 2 catch console ( ) function main This file contains two test functions and a function that is calling those test functions. At the bottom of the file, we are executing the function. This script expects two command line arguments: main main spreadsheetId (this is the ID that we got from step 1) sheetName (this is the name of the worksheet for which you want to see the values. When you create a new spreadsheet, it is ) Sheet1 Also, ensure that the env variables and are set properly. GCLOUD_PROJECT GOOGLE_APPLICATION_CREDENTIALS Now, from the terminal, run this script .js node test <your google sheet's spreadsheet id> <sheet name of the worksheet> If you have followed all the steps correctly, you should see output like this: output getSpreadSheet { : , : { : , : , : , : , : { : { : , : , : }, : { : , : , : , : }, : , : , : { : {}, : , : , : , : , : , : }
    }
  }, : [
    { : { : , : , : , : , : { : , : }
      }
    }
  ], : }
output getSpreadSheetValues { : , : , : [
    [ , , ],
    [ , , ],
    [ , , ],
    [ , , ],
    [ , , ]
  ]
} for "spreadsheetId" "1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY" "properties" "title" "test-sheet" "locale" "en_US" "autoRecalc" "ON_CHANGE" "timeZone" "Asia/Calcutta" "defaultFormat" "backgroundColor" "red" 1 "green" 1 "blue" 1 "padding" "top" 2 "right" 3 "bottom" 2 "left" 3 "verticalAlignment" "BOTTOM" "wrapStrategy" "OVERFLOW_CELL" "textFormat" "foregroundColor" "fontFamily" "arial,sans,sans-serif" "fontSize" 10 "bold" false "italic" false "strikethrough" false "underline" false "sheets" "properties" "sheetId" 0 "title" "Sheet1" "index" 0 "sheetType" "GRID" "gridProperties" "rowCount" 1000 "columnCount" 26 "spreadsheetUrl" "https://docs.google.com/spreadsheets/d/1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY/edit" for "range" "Sheet1!A1:Z1000" "majorDimension" "ROWS" "values" "Name" "Country" "Age" "John" "England" "30" "Jane" "Scotland" "23" "Bob" "USA" "45" "Alice" "India" "33" If you get an error, it means you have not followed all the steps correctly. For this tutorial, the version of npm module was . You might face issues if you are using older version of the module. Make sure the spreadsheetId and sheetname are correct and the enviroment variables are set properly. If you still get error, you should check the error message and code to see what might be causing the problem. googleapis 43.0.0 References Documentation for Google API Node.js client. https://github.com/googleapis/google-api-nodejs-client Official Google Sheets API reference. https://developers.google.com/sheets/api/reference/rest I would definitely recommend checking out these references (especially the  Official Google Sheets API reference) to get a more in depth understanding of the sheets API and how to use the Node.js client. Hope you found this tutorial helpful. Thanks and happy coding :-) Previously published at http://codingfundas.com/how-to-read-edit-google-sheets-using-node-js/index.html

Fetch

Google

Read My Stories

Too Long; Didn't Read

Get free API security automated scan in minutes

How to Use Google Sheets API with Nodejs

Too Long; Didn't Read

Companies Mentioned

@mandeep_m91

RELATED STORIES