Authenticate AWS lambda against Google Sheets API

Question

I am trying to create an aws lambda function that will read rows from multiple Google Sheets documents using the Google Sheet API and will merge them afterwards and write in another spreadsheet. To do so I did all the necessary steps according to several tutorials:

Create credentials for the AWS user to have the key pair.
Create a Google Service Account, download the credentials.json file.
Share each necessary spreadsheet with the Google Service Account client_email.

When executing the program locally it works perfectly, it successfully logins using the credentials.json file and reads & writes all necessary documents.

However when uploading it to AWS Lambda using the serverless framework and google-spreadsheet, the program fails silently in the authentication step. I've tried changing the permissions as recommended in this question but it still fail. The file is read properly and I can print it to the console.

This is the simplified code:

async function getData(spreadsheet, psychologistName) {
    await spreadsheet.useServiceAccountAuth(clientSecret);

    // It never gets to this point, it fails silently

    await spreadsheet.loadInfo();

    ... etc ...    
}


async function main() {
    const promises = Object.entries(psychologistSheetIDs).map(async (psychologistSheetIdPair) => {
        const [psychologistName, googleSheetId] = psychologistSheetIdPair;
        const sheet = new GoogleSpreadsheet(googleSheetId);
        psychologistScheduleData = await getData(sheet, psychologistName);
        return psychologistScheduleData;
    });
    //When all sheets are available, merge their data and write back in joint view.
    Promise.all(promises).then(async (psychologistSchedules) => {
        ... merge the data ...
    });
}


module.exports.main = async (event, context, callback) => {
  const result = await main();
  return {
    statusCode: 200,
    body: JSON.stringify(
      result,
      null,
      2
    ),
  };

Urco · Accepted Answer · 2020-06-17 12:34:02Z

1

I solved it,

While locally having a Promise.all(promises).then(result =>...) eventually returned the value and executed what was inside the then(), aws lambda returned before the promises were resolved.

This solved it:

const res = await Promise.all(promises);
mergeData(res);

answered Jun 17, 2020 at 12:34

Urco

3653 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Authenticate AWS lambda against Google Sheets API

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related