Using Google Sheets API on Google Cloud Functions, getClient() not working, function timesout

Question

I have been trying to get Google Cloud Functions to obtain the default service account credentials automatically with getClient() but when I use it the function is unresponsive and eventually times out. I am using the Google Sheets API just to obtain a value for now.

I have made sure to grant the service account the right access, share the sheets with the service email, checked my dependencies.

I have seen a few answers and guides (here and here) using getClient but I don't seem to be able to get it to work, here is my code:

const {google} = require('googleapis');
const express = require('express');
const bodyParser = require('body-parser');

const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: true}));

const PORT = process.env.PORT || 8080;
const SHEET_ID = "1ie8......lvnaKM";
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

app.get('/', (req, res) => {

    let range = 'test2!B1';
    getSheetsValue(SHEET_ID, range, res);

});

app.listen(PORT, () => { });

exports.sheetsTest = app;

async function getSheetsValue(sheetID, range, res) {

    const authClient = await google.auth.getClient({
        scopes: SCOPES,
    });
    console.log(authClient);
    const sheets = google.sheets({
        version: 'v4',
        authClient
    });
    request = {
        spreadsheetId: sheetID,
        range: range,
    };
    try {
        const result = await sheets.spreadsheets.values.get(request);
        console.log(`result: ${JSON.stringify(result)}`);
        console.log(`result.data: ${JSON.stringify(result.data)}`);
        res.json(result.data.values[0]);
    } catch (e) {
        return e;
    }
}

I've even tried it with the google-auth-library per instructions here but it didn't work: https://github.com/googleapis/google-auth-library-nodejs#application-default-credentials

Until then hard coding the credentials into the program works, I was just hoping to figure out why this does not work. Ideally I'd like use the implicit credentials

Here is the code that works on Google Cloud Functions for me:

const {google} = require('googleapis');
const express = require('express');
const bodyParser = require('body-parser');

const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: true}));

const PORT = process.env.PORT || 8080;
const SHEET_ID = "SHEET_ID";
const CLIENT_EMAIL = "SERVICE_ACCOUNT_EMAIL";
const PRIVATE_KEY = "KEY"; //Credentials from the JSON file after downloading it
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

app.get('/', (req, res) => {

    let range = 'test2!B1';
    getValue(SHEET_ID, range, res);
});

app.listen(PORT, () => { });

exports.sheetsTest = app;

async function getValue(sheetID, range, res) {

    let authClient = new google.auth.JWT(CLIENT_EMAIL, null, PRIVATE_KEY, SCOPES);
    const sheets = google.sheets({
        version: 'v4',
        auth: authClient,
    });
    request = {
        spreadsheetId: sheetID,
        range: range,
    }
    try {
        const result = await sheets.spreadsheets.values.get(request);
        res.json(result.data.values[0]);
    } catch (e) {
        return e;
    }
}

Thank you

Raserhin Raserhin · Accepted Answer · 2019-12-06T13:35:20

First of all you should make sure that you have created a variable in your PATH named GOOGLE_APPLICATION_CREDENTIALS that should point to your credentials JSON, if you need more information you can check here how to do it. If you don't do that the getClient() will not work.

Regarding your code, you seem to be doing something weird over here:

 const authClient = await google.auth.getClient({
        scopes: SCOPES,
    });

If you look over to the documentation of the authentication method you are trying to use, you will see that your code doesn't look like that. Change it to something like this.

 const auth = new GoogleAuth({
        scopes: SCOPES
 });

 const authClient = await auth.getClient();

But now you need to add this class to your script so add the following line over the "imports":

const {GoogleAuth} = require('google-auth-library');

The last thing that you got wrong is that in this fragment of your code:

 const sheets = google.sheets({
        version: 'v4',
        authClient
    });

you don't seem to actually inform what property the variable authClient refers to. So you should change it to

 const sheets = google.sheets({
        version: 'v4',
        auth: authClient
    });

So for me the final code looks something like this:

const {google} = require('googleapis');
const {GoogleAuth} = require('google-auth-library');
const express = require('express');
const bodyParser = require('body-parser');

const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: true}));

const PORT = process.env.PORT || 8080;
const SHEET_ID = "<your sheet ID>";
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

app.get('/', (req, res) => {

    let range = 'test2!B1';
    getSheetsValue(SHEET_ID, range, res);

});

app.listen(PORT, () => { });

exports.sheetsTest = app;

async function getSheetsValue(sheetID, range, res) {

    const auth = new GoogleAuth({
        scopes: SCOPES
      });

    const authClient = await auth.getClient();
    // console.log(authClient);
    const sheets = google.sheets({
        version: 'v4',
        auth: authClient
    });
    request = {
        spreadsheetId: sheetID,
        range: range,
    };
    try {
        const result = await sheets.spreadsheets.values.get(request);
        console.log(`result: ${JSON.stringify(result)}`);
        console.log(`result.data: ${JSON.stringify(result.data)}`);
        res.json(result.data.values[0]);
    } catch (e) {
        console.log(e);
        return e;
    }
}

Using Google Sheets API on Google Cloud Functions, getClient() not working, function timesout

1 Answers