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

google sheets api javascript
google sheets api pricing
google sheets api python
cloud function google sheets
google sheets api android
google sheets rest api
google sheets api nodejs
google sheets api typescript

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?

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!

Cloud Function to Automate CSV data import into Google Sheets, A Cloud Function to automate Google Spreadsheet CSV import In this post I'll walk you through how to write a Cloud Function that reacts Change the function body to use the Cloud Storage and Sheets APIs, Get unlimited access to the best stories on Medium — and support writers while you're at it. 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.

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.

A Cloud Function to automate Google Spreadsheet CSV import, a blog post of the same name: http://pipetree.com/qmacro/blog/2017/04/30/​google-cloud Duration: 8:22 Posted: May 1, 2017 Using the Gmail Push Notification API you can send Gmail events to a Cloud Pub/Sub topic and consume them with a Cloud Function. Send feedback Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License , and code samples are licensed under the Apache 2.0 License .

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

Google Opens API for Google Docs, <http://flask.pocoo.org/docs/1.0/api/#flask.Request> Returns: The response text, or any set of values that can be turned into a Response object using  Learn how to use Cloud Functions, Cloud Storage, Cloud Vision API, Cloud Translation API, and Cloud Pub/Sub to upload images, extract text, translate the text, and save the translations. Slack Tutorial - Slash Commands. Learn how to use Cloud Functions to implement a Slack Slash Command that searches the Google Knowledge Graph API.

Pricing | Google Docs API, Cloud Functions minimizes boilerplate code, making it easier to use Firebase and Google Cloud inside your function. Zero maintenance, Deploy your JavaScript or  Step 1: Turn on the Google Sheets API. Click this button to create a new Cloud Platform project and automatically enable the Google Sheets API: Enable the Google Sheets API. Take note of the Client ID. shown in the resulting dialog. Then click the following button to create an API key in the same project: Create API key. Take note of the API Key shown in the resulting dialog.

Browser Quickstart | Sheets API, 3 Approaches for Using the Google Sheets API in Node.js: A Tutorial After learning to code for the past year, I've recently found myself in the During my most recent review of the material, I realized the only way that it  API tutorial for beginners: what is Apps Script? In this API tutorial for beginners, we’ll use Google Apps Script to connect to external APIs.. Google Apps Script is a Javascript-based scripting language hosted and run on Google servers, that extends the functionality of Google Apps.

Sheets API, Using the Google Sheets API from Google, and…. can you use Google Note that for the above, we will be using Node.js (in JavaScript), to create We can start off by creating a project on Google Cloud Console, followed Column A was to be the user's email, while column B would be the user's name. Click this button to create a new Cloud Platform project and automatically enable the Google Sheets API: Enable the Google Sheets API. In resulting dialog click DOWNLOAD CLIENT CONFIGURATION and save the file credentials.json to your working directory. Step 2: Install the Google Client Library. Run the following command to install the library using pip: pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Comments
  • 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'.
  • 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?
  • 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.
  • 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)
  • @CodeKadiya does the answer from Matt below work for you?
  • Is there a python equivalent to this?
  • @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
  • I have tried this with no luck and posted the details here: stackoverflow.com/questions/51886522/…
  • @Matt, got it, thanks! I will update my answer and point to yours. I really like your approach.
  • @domdomegg thanks for updating this
  • You need to add more details about how you solved the problem without link (the link might get broken in future).