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?

Solution 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!

Solution 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

Solution 3

I've been browsing the web for hours looking for help about how integrate Google Sheets API with Firebase Cloud Functions.

Luckily I've found this post on Medium that deals with this exact topic:

https://medium.com/@elon.danziger/fast-flexible-and-free-visualizing-newborn-health-data-with-firebase-nodejs-and-google-sheets-1f73465a18bc

Comments

[IROEGBU]

You need to add more details about how you solved the problem without link (the link might get broken in future).

[Axel]

Maybe this works by adding the Sheet API via NPM manually on a local copy of Node.JS but this function is NOT deployable in Google, I've tried but the Cloud environment doesn't recognize 'googleapis'.

[Jan Krynauw]

Is there a python equivalent to this?

[Matt]

@JanKrynauw I don't know Python, but it looks like you might be able to take this example but use google-auth instead of oauth2client

[Jan Krynauw]

I have tried this with no luck and posted the details here: stackoverflow.com/questions/51886522/…

[Martin Omander]

Axel, the code above works for me when I deploy it to Google. You mentioned that the server doesn't recognize "googleapis". I got that error too at first, when I had left out the curly braces around "google" on the very first line above. Another thing to check: did you npm install googleapis so it went into your package-lock.json file, and was deployed together with your code?

[Martin Omander]

@Matt, I like your auto-credentials approach! But when I run your code I get the error "TypeError: Cannot read property 'getRoot' of undefined" on the line "return getSheets(...)". My package.json file has "googleapis": "^33.0.0". Any thoughts?

[Matt]

@MartinOmander I think newer versions of sheets reference this - so just need to bind it in promisify. E.g. const getSheets = promisify(api.spreadsheets.get.bind(api.spreadsheets)); - I haven't had a chance to test this yet

[Martin Omander]

@Matt, got it, thanks! I will update my answer and point to yours. I really like your approach.

[Axel]

Yes, that's how I resolved the issue as well. The code does need a little updating to work with the new APIs. It's been a while since I used this and I forget the things that needed to be done differently, especially in the Google console, but I got everything working and it's in production now.

[Code Kadiya]

I got this error Error: Requested entity was not found. at Gaxios.request (/srv/node_modules/gaxios/build/src/gaxios.js:70:23) at <anonymous> at process._tickDomainCallback (internal/process/next_tick.js:229:7)

[Martin Omander]

@CodeKadiya does the answer from Matt below work for you?

[Jamey Kirby]

Thanks for this post. Got my spreadsheet updating withing a couple of minutes after reading this. Def. an acceptable answer.

[Lydia halls]

I have the same error as Code Kadiya: Error: Requests from referer <empty> are blocked. at Gaxios.<anonymous> (/srv/node_modules/gaxios/build/src/gaxios.js:73:27) any idea ?

[go_diego]

This approach worked with Netlify Functions. Just set the needed credentials as env variables. Thanks!

[Charles Harring]

here is the cached link in case it ever does break, another option would be internet archive webcache.googleusercontent.com/…

[RoShan Shan]

Still working until now. Very simple and easy to understand. Thanks guy alot!

[Shajeel Afzal]

The option to get API key is not available anymore in the Google Cloud Console, or I guess it is moved somewhere else.

[MacD]

The option to create an "API key" is on the main credentials item, not under the google sheets api credentials item. Took me a while to catch that. Was getting an "Error: API key not valid" error. Still working as of Nov, 2021

[zakdances]

What is the "main credentials item"? I still don't see any "API key" button.

[Nisala]

This answer still works! Far easier than creating your own credentials and uploading them, like the top answer does. Note that if your spreadsheet is private, you'll still have to do step 5 from the top answer. The service account used should just be the App Engine default service account.