How do I access the result of a callback function from Google Sheets API?

Question

I'm using Google Sheets API to copy a sheet, and I want to pass the ID of the new sheet into a variable.

I can successfully get the sheet ID within the callback function (using result.data.sheetId), but I'm struggling to use it outside of the function.

I'm using async / await syntax, and I have also experimented with utils.promisify, with no luck!

async function copySheet(jwt, apiKey, spreadsheetId, range) {

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

  let newSheetId = await sheets.spreadsheets.sheets.copyTo({
    "spreadsheetId": spreadsheetId,
    "sheetId": 0,
    "auth": jwt,
    "key": apiKey,
    "resource": {
      "destinationSpreadsheetId": spreadsheetId,
    }
  }, function (err, result) {
    if (err) {
      throw err;
    }
    console.log(result.data.sheetId);
    return result.data.sheetId;
  });

  console.log(newSheetId);

}

The first console.log shows the number I want, but the second logs undefined.

Do you have any suggestions to allow me to use the sheetId outside of the callback function?

Tanaike · Accepted Answer · 2019-05-27 12:21:01Z

How about this modification? Please think of this as just one of several answers.

Modified script:

Please modify your script as follows.

From:

let newSheetId = await sheets.spreadsheets.sheets.copyTo({
  "spreadsheetId": spreadsheetId,
  "sheetId": 0,
  "auth": jwt,
  "key": apiKey,
  "resource": {
    "destinationSpreadsheetId": spreadsheetId,
  }
}, function (err, result) {
  if (err) {
    throw err;
  }
  console.log(result.data.sheetId);
  return result.data.sheetId;
});

console.log(newSheetId);

To:

let res = await sheets.spreadsheets.sheets.copyTo({
  "spreadsheetId": spreadsheetId,
  "sheetId": 0,
  "auth": jwt,
  "key": apiKey,
  "resource": {
    "destinationSpreadsheetId": spreadsheetId,
  }
});

let newSheetId = res.data.sheetId;
console.log(newSheetId);

Note:

From your question, I could confirm that you have already been able to use Sheets API.
spreadsheets.sheets.copyTo() uses POST method. So in this case, API key cannot be used. I think that even when key: apiKey is removed, the script works.

Reference:

spreadsheets.sheets.copyTo

If this was not the result you want, I apologize.

Collectives™ on Stack Overflow

How do I access the result of a callback function from Google Sheets API?

1 Answer 1

Modified script:

From:

To:

Note:

Reference:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

From:

To:

Note:

Reference:

Comments

Your Answer

Sign up or log in

Post as a guest

Related