How to use Google sheets API while inside a google cloud function

Question

I'm trying out Google's Cloud Functions service and I want to read and write a Google Spreadsheets but can't seem to find any examples or ways to do this.

My problem steams from the fact that the example javascript for a Google cloud function is:

exports.helloWorld = function helloWorld (req, res) {   res.send(`Hello ${req.body.name || 'World'}!`); }; 

This works but I want to do what google has as a example to read from a Google spreadsheet:

  gapi.load('client:auth2', initClient);    function initClient() {     gapi.client.init({       discoveryDocs: DISCOVERY_DOCS,       clientId: CLIENT_ID,       scope: SCOPES     }).then(function () {       // Listen for sign-in state changes.       gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);        // Handle the initial sign-in state.               gapi.client.sheets.spreadsheets.values.get({       spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',       range: 'Class Data!A2:E',     }).then(function(response) {       var range = response.result;       if (range.values.length > 0) {         appendPre('Name, Major:');         for (i = 0; i < range.values.length; i++) {           var row = range.values[i];           // Print columns A and E, which correspond to indices 0 and 4.           appendPre(row[0] + ', ' + row[4]);         }       } else {         appendPre('No data found.');       }     }, function(response) {       appendPre('Error: ' + response.result.error.message);     });     });   } 

Does anyone know if this is possible or an example that shows how to do something similar to this?

Answer 1

Here is how I did it with Google Cloud Functions. I figured that OAuth wouldn't be a good fit, as Cloud Functions often run unattended. Fortunately there are service accounts, meant for machine-to-machine communication.

---

1. Create a service account in your Cloud project

---

2. Save the service account key

In step 1, a key file in JSON format was downloaded on your computer. Save it in your project directory and rename it credentials.json.

---

3. Create an API key

---

4. Save the API key

Copy and save the API key from step 3 in a file called api_key.json in your project directory. It should look like this:

{   "key": "<PASTE YOUR API KEY HERE>" } 

---

5. Grant spreadsheet access to the service account

Share the spreadsheet with the service account email created in step 1.

---

6. Call Google's Sheets API

Here is my code which appends a row to the spreadsheet each time the Cloud Function is called.

const {google} = require('googleapis');  exports.reply = (req, res) => {   var jwt = getJwt();   var apiKey = getApiKey();   var spreadsheetId = '<PASTE YOUR SPREADSHEET ID HERE>';   var range = 'A1';   var row = [new Date(), 'A Cloud Function was here'];   appendSheetRow(jwt, apiKey, spreadsheetId, range, row);   res.status(200).type('text/plain').end('OK'); };  function getJwt() {   var credentials = require("./credentials.json");   return new google.auth.JWT(     credentials.client_email, null, credentials.private_key,     ['https://www.googleapis.com/auth/spreadsheets']   ); }  function getApiKey() {   var apiKeyFile = require("./api_key.json");   return apiKeyFile.key; }  function appendSheetRow(jwt, apiKey, spreadsheetId, range, row) {   const sheets = google.sheets({version: 'v4'});   sheets.spreadsheets.values.append({     spreadsheetId: spreadsheetId,     range: range,     auth: jwt,     key: apiKey,     valueInputOption: 'RAW',     resource: {values: [row]}   }, function(err, result) {     if (err) {       throw err;     }     else {       console.log('Updated sheet: ' + result.data.updates.updatedRange);     }   }); } 

---

Hope this helps!

Answer 2

I've found a way to do this in the same auto-authenticated way that the other services can.

In the NodeJS API README, they have an auto-auth example that we can apply to the Sheets API like so:

index.js:

const { google } = require('googleapis'); const { promisify } = require('util');  exports.main = (req, res) => {   google.auth.getClient({     scopes: ['https://www.googleapis.com/auth/spreadsheets'],   }).then(auth => {     const api = google.sheets({ version: 'v4', auth });     const getSheets = promisify(api.spreadsheets.get.bind(api.spreadsheets));     return getSheets({ spreadsheetId: 'SPREADSHEET_ID' });   })     // This just prints out all Worksheet names as an example     .then(({ data: { sheets } }) => {       res.status(200).send({ sheets });     })     .catch(err => {       res.status(500).send({ err });     }) }; 

package.json:

{   "dependencies": {     "googleapis": "^42"   } } 

Finally, share the sheet with the email address of the service account running the cloud function.

google.auth.getClient in that example will detect the credentials for the Service Account that is attached to the Cloud Function. So you can interact with Sheets API without having to manage any auth secrets.

Local Development

You can use this same code for local development if you set the .env variable GOOGLE_APPLICATION_CREDENTIALS to the path of the service account credentials JSON file.

For example, a JWT auth client will be created when your code is running on your local developer machine, and a Compute client will be created when the same code is running on a configured instance of Google Compute Engine

https://github.com/googleapis/google-api-nodejs-client#service-to-service-authentication

GOOGLE_APPLICATION_CREDENTIALS="/path/to/myapp-375951-sa01517d6251.json" 

https://cloud.google.com/docs/authentication/getting-started#setting_the_environment_variable