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

I was able to get this working with the following simpler Python Cloud Function solution that adds some additional rows of data to the end of a Sheet document and was all set up under a single Google account. I assume something similar would also work in the other supported Cloud Function languages.

1. Enable the Google Sheets API on your project.

2. Go to APIs & Services > Credentials and create a new service account for the Cloud Function with the Browser role.

3. Share the Google Sheet with the email address of the new service account for the Cloud Function with the Editor role eg. spreadsheet-writer@project.iam.gserviceaccount.com

4. Create your Cloud Function with the Runtime service account set to the new service account with the following Python code:

requirements.txt

functions-framework==3.*
google-api-python-client==2.*

main.py

import functions_framework
from googleapiclient.discovery import build

@functions_framework.http
def func(request):
  service = build('sheets', 'v4')

  result = service.spreadsheets().values().append(
    spreadsheetId='<YOUR SPREADSHEET ID>',
    range=f'<SHEET NAME>!A:C',
    valueInputOption='USER_ENTERED',
    insertDataOption='INSERT_ROWS',
    body={
      'values': [
        ['row 1 column A value', 'row 1 column B value', 'row 1 column C value'],
        ['row 2 column A value', 'row 2 column B value', 'row 2 column C value'],
      ]
    }
  ).execute()

Once deployed automatic authentication is made with the Runtime service account.

Answer 2

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')

Answer 3

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!

Answer 4

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

Answer 5

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 6

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