await sheets.spreadsheets.get does not return spreadsheet data

[virusakos]

I have a google cloud function which is being executed on a Pub/Sub publish event.
The purpose of the function is to perform updates in various spreadsheets and the sequence of calls to update the spreadsheets is:

1. sheets.spreadsheets.values.update (which is used to update a spreadsheet range with values),
2. sheets.spreadsheets.get (which is used to retrieve sheet and named range ids) and
3. sheets.spreadsheets.batchUpdate (which is used to update a named range)

Initially I created the function in a way that it was updating all spreadsheets in parallel and it was throwing 'read ECONNRESET' (but it was not consistent, there were times I was receiving the error at the start of the function execution, other times after a few calls in values.update or after a few calls in batchUpdate, etc).

I thought that I was doing a lot of requests in parallel and that was causing the issue so the next thing I wanted to try was to change my function to work in a 'synchronous' way, i.e. for each spreadsheet call values.update, get and batchUpdate and then move on to the next spreadsheet, so I used async/await ( and this is the first time actually I am working with async/await so if you see that I am doing something stupid please be gentle :) )

• Node.js version: 8.11.1
• googleapis version: 34.0.0

Here is the code I am using which does not work:

const {google} = require('googleapis');
...
const sheets = google.sheets({version: 'v4', auth: createClient(require('xxx.json'))});
...
function createClient (json) {
  const client = new google.auth.JWT(
    json.client_email,
    null,
    json.private_key,
    ['https://www.googleapis.com/auth/drive']
  );
  client.authorize(function (err, tokens) {
    if (err) { console.log(err); }
  });
  return client;
}
...
const spreadsheetInfo = getSpreadsheetInfo({SpreadsheetId: xxxxx});
...
async function getSpreadsheetInfo (params) {
  const request = {
    spreadsheetId: params.SpreadsheetId,
    ranges: [],
    includeGridData: false
  };

  const response = await sheets.spreadsheets.get(request, {maxContentLength: -1});
  return response.data; // This returns undefined
  // I have also tried to log the response output to see
  // if the spreadsheet object is in a different property but it is not
}

Here is the 'old fashion' code that works as expected:

const {google} = require('googleapis');
...
const sheets = google.sheets({version: 'v4', auth: createClient(require('xxx.json'))});
...
function createClient (json) {
  const client = new google.auth.JWT(
    json.client_email,
    null,
    json.private_key,
    ['https://www.googleapis.com/auth/drive']
  );
  client.authorize(function (err, tokens) {
    if (err) { console.log(err); }
  });
  return client;
}
...
getSpreadsheetInfo({SpreadsheetId: xxxxx})
  .then((response) => {
    console.log(response); // This logs spreadsheet object correct
  })
  .catch((err) => {
    console.log(err);
  });
...
function getSpreadsheetInfo (params) {
  return new Promise((resolve, reject) => {
    const request = {
      spreadsheetId: params.SpreadsheetId,
      ranges: [],
      includeGridData: false
    };

    sheets.spreadsheets.get(request, {maxContentLength: -1}, (err, response) => {
      if (err) {
        reject(err);
      } else {
        resolve(response);
      }
    });
  });
}

Can anyone understand what I am doing wrong?

[ajaaym]

shouldnt this return response.data; // This returns undefined just be return response? Api returns spreadsheet objects in response directly.

[virusakos]

@ajaaym as far as I have understood, response is used in the 'old fashion' way, i.e. with callbacks.
When you are making calls with async/await response.data should be used.

But I have already tried with return response; and still I do not get the spreadsheet object.

[ajaaym]

@virusakos I just tried below example and its working fine.

const {google} = require('googleapis');
var sheets = google.sheets('v4');

const fs = require('fs');
const path = require('path');
const http = require('http');
const url = require('url');
const querystring = require('querystring');
const opn = require('opn');
const destroyer = require('server-destroy');

/**
 * Create a new OAuth2 client with the configured keys.
 */
const oauth2Client = new google.auth.OAuth2(
    'CLIENT_ID',
    'CLIENT_SECRET',
    'http://localhost:3000/oauth2callback'
  );



async function authenticate(scopes) {
    return new Promise((resolve, reject) => {
      // grab the url that will be used for authorization
      const authorizeUrl = oauth2Client.generateAuthUrl({
        access_type: 'offline',
        scope: scopes.join(' '),
      });
      const server = http
        .createServer(async (req, res) => {
          try {
            if (req.url.indexOf('/oauth2callback') > -1) {
              const qs = querystring.parse(url.parse(req.url).query);
              res.end('Authentication successful! Please return to the console.');
              server.destroy();
              const {tokens} = await oauth2Client.getToken(qs.code);
              oauth2Client.credentials = tokens;
              resolve(oauth2Client);
            }
          } catch (e) {
            reject(e);
          }
        })
        .listen(3000, () => {
          // open the browser to the authorize url to start the workflow
          opn(authorizeUrl, {wait: false}).then(cp => cp.unref());
        });
      destroyer(server);
    });
  }

  const scopes = [
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/drive.file',
    'https://www.googleapis.com/auth/drive.readonly',
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/spreadsheets.readonly'
  ];
  
  async function runSample(authClient) {
    var request = {
      // The spreadsheet to request.
      spreadsheetId: 'MY_SPREADSHEET_ID',  // TODO: Update placeholder value.
  
      // The ranges to retrieve from the spreadsheet.
      ranges: [],  // TODO: Update placeholder value.
  
      // True if grid data should be returned.
      // This parameter is ignored if a field mask was set in the request.
      includeGridData: false,  // TODO: Update placeholder value.
  
      auth: authClient,
    };
  
    const response = await sheets.spreadsheets.get(request);
    return response.data;
  };

  authenticate(scopes)
  .then(async client => {
    const sheet =  await runSample(client);
    console.log('sheet id: ', sheet.spreadsheetId);
    console.log('sheet url: ', sheet.spreadsheetUrl);
  })
  .catch(console.error);

[virusakos]

@ajaaym thank you for your time and your test!

I see you call runSample with await runSample(client).
Seems that this is what I was missing in my code.
I am calling my function with const spreadsheetInfo = getSpreadsheetInfo({SpreadsheetId: xxxxx});.
If I change it to const spreadsheetInfo = await getSpreadsheetInfo({SpreadsheetId: xxxxx}); then everything is working as expected.

Many thanks!