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?

Hedy

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!

Dowlen

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'. – Appaloosa

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? – Dowlen

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. – Appaloosa

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) – Walkup

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

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 ? – Nigelniger

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

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

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

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 – Devondevona

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

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

Prisoner

Is there a python equivalent to this? – Discontinuity

@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 – Prisoner

I have tried this with no luck and posted the details here: #51887022 – Discontinuity

@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? – Dowlen

@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 – Prisoner

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

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

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. – Bitner

Your service account will have multiple email addresses. For me only the App Engine default service account email worked. It should be ending with appspot.gserviceaccount.com – Vive

Answer 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

Phonon

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

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

Answer 4

For those who are interested in a solution in Python, I have written a small class that should help you get stareted:

# pip install oauth2client
# pip install google-api-python-client

# sheet_id: is the ID of the sheet obtainable from the sheet URL
# credentials_file: is the json object downloaded from the service account

# Note: You need to share your google sheet with the email address of the service account for this to work.

from googleapiclient.discovery import build
from google.oauth2 import service_account

class GoogleSheetManager:
    def __init__(self, sheet_id, credentials_file):
        self.sheet_id = sheet_id
        credentials = service_account.Credentials.from_service_account_file(credentials_file)  
        self.service = build('sheets', 'v4', credentials=credentials)

    def read_sheet(self, sheet_name):
        range_ = f'{sheet_name}!A1:Z'
        result = self.service.spreadsheets().values().get(
            spreadsheetId=self.sheet_id,
            range=range_,
        ).execute()
        values = result.get('values', [])
        return values # containing both the header and all the rows

    def write_sheet(self, sheet_name, values):
        range_ = f'{sheet_name}!A1:Z'
        result = self.service.spreadsheets().values().update(
            spreadsheetId=self.sheet_id,
            range=range_,
            valueInputOption='USER_ENTERED',
            body={'values': values}
        ).execute()
        return result.get('updatedCells')

Mondragon

Answer 5

2023 Answer / Apps Script Alternative

Howdy devs,

So I've been down this road a number of times and, in my experience, connecting an app / client with Cloud Functions and Sheets is absolutely awful. I've had extensive problems with:

• Permissioning
• Auth inside the Cloud Function
• Setting up .env variables and Firebase config variables
• ...and more.

Good news! There is an alternative that will work for many of you -- using Apps Script as the intermediary rather than Cloud Functions.

Here's the basic rundown of the alternative:

1. Create a Google Sheet and get into the Apps Script backend
2. Bring the Google Firebase Apps Script library into the script
3. Use the above library to pull data, write data, etc.

Code Sample

const getFirestore = () => {
  return FirestoreApp.getFirestore(serviceAccountEmail, serviceAccountPrivateKey, projectId)
}

/** Imports a collection from Firestore
 * @param {string} collectionName The collection to import.
 */
const importFromFirestore = (collectionName) => {
 // Gets a Firestore instance
 const firestore = getFirestore()

 // Gets all documents from a collection
 const documents = firestore.getDocuments("test").map(doc => {
  console.log(doc.fields)
 })
}

Basically, you're just using Apps Script as the logistical hub rather than Cloud Functions.

Also, here's a video that shows you how to do this. I was able to pull data from my Firestore Database in about 4 minutes after spending 10+ hours working through many other approaches.

There are some limitations to this approach, but for many of you it will achieve everything you're looking to achieve by integrating Cloud Functions with Sheets.

Hope this helps someone!

Brief