Triggers AWS Lambda Every Time a New Google Sheet Row is Added

user_14498141 0
Viewed 478

I have a google sheet that collects answer from a google form that ask user various question. Every time a form is submitted by user, a new row will be added to the google sheet.

I want to write AWS Lambda function such that every time a new response appear, the Lambda Function will run and append the new response to the RDS MySql Database.

I use Python to write script. Most of the stuff that I see out there are using node.js I'm not sure where to begin.

Thanks

aws-lambda google-sheets amazon-web-services python
2 Answers
user_130451930

Depending on how your Google Sheet is configured, you can use the onEdit() trigger in combination with this Google Apps Script library, which has full support for the AWS SDK.

Your final function could look something like this:

// define your AWS configuration here (see dist/Config.js in the library example)

const AWS_CONFIG = {
  accessKey: 'AK0ZXZD0KGNG4KG6REBP', // use your own AWS key
  secretKey: 'EXrPgHC41HEW2YownLUnJLgh6bMsrmW1uva1ic24', // use your own AWS key
  region: 'us-east-1',
};

function initConfig(config) {
  AWS.config = new AWS.Config();
  AWS.config.update({
    region: config.region,
    sslEnabled: true,
    credentials: {
      accessKeyId: config.accessKey,
      secretAccessKey: config.secretKey,
    },
  });
}

// adopted from the (see dist/Examples.js in the library example)

async function invokeLambdaHelloWorld(payload) {
  initConfig(AWS_CONFIG);
  var result = await invokeLambda('helloWorld', payload);
  if (result === false) {
    return false;
  }

  return result;
}

async function onEdit(e) {
  // Set a comment on the edited cell to indicate when it was changed.
  const range = e.range;

  // Add some checks here to determine whether or not you need to call
  // the lambda function.

  // Prepare the payload

  const payload = {
    key1: 'value1',
    key2: 'value2',
    key3: 'value3',
  };

  // Call the lambda function and get the response

  const resp = await invokeLambdaHelloWorld(payload);
  Logger.log(resp);
}
user_46267700
Related