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 · Accepted Answer · 2019-12-06 13:35:20Z

1

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;
    }
}

answered Dec 6, 2019 at 13:35

Raserhin

2,7061 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

B.Ang Over a year ago

Sorry I wasnt clear initially. The documentation I provided was 1 of the other ways I tried. I did implement your changes but still doesnt work. The reason I had my code that way was cause I was followin here and here Could you also explain how to set the PATH to the credentials? I thought the point of getClient() is to use the App Engine service account credentials tied to the function without providing path or file

Raserhin Over a year ago

I would suggest to take a look at the nodeJs google auth documentation. There are explanation and code examples of how the authorization process work. Also for the PATH variable take a look at here. Also it would be helpful to share the error message, or information of how is your environment set up. Is this executed in a "google environment" like GCP, or App Engine?

B.Ang Over a year ago

Thank you, I do not know why it didn't work before but I revisited the issue and implemented your changes and as per the google auth documentation and it worked. For anybody who is concerned, using the getClient as opposed to hardcoded credentials adds anywhere from 0-100ms in processing time. I tried moving it outside the request function as a global var but it didn't work

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related