Automate to Google Spreadsheets Integration

Automate App to Google Spreadsheets Integration.md

Introduction

This is a sample web app implemetation in Google Apps Script that receives a JSON payload and inserts it directly into a Google spreadsheet. It is a demo implementation for Automate app flow Google Spreadsheets Integration.

Usage

Using just one HTTP Request block in an Automate flow you can send data from your Automate flows directly into Google Spreadsheets! Pretty neat, isn't it?

Sample Spreadsheet

https://docs.google.com/spreadsheets/d/1VZEp-ru9BH9AQSNHzRxnJZx1Nabviz6UiN9PIelOjA8

Setup

1. Create new Spreadsheet sheets.new
2. Make sure there are two sheets one with name data and called logs as in the sample spreadsheet
3. Go to Extensions -> Apps Script
4. Copy the below code
5. Save the project
6. Run the doGet function from the menu, you need to authorize it first
7. Authorize the script
8. Click Deploy -> New Deployment
9. Select Type -> Web app
10. Execute As -> Me (the web app will be exexuted using your account)
11. Who has access -> Anyone (no authorization needed and you can invoke the web app directly)
12. Deploy
13. Copy the URL
14. Paste it into the Automate flow in the HTTP request block
15. Run the flow
16. Enjoy ;o)

Few tips when working with Google Apps script services. I have been using it for ages and I did make few add-ons for Google Spreadsheets. Check AnyMerge to try them out.

Google Apps Script Tips:

1. Do not place resource extensive code directly into the doGet/doPost handlers. For example, if you want to create a Google Doc containing the data from Automate. Every execution of the web app counts towards your free daily quota. Quota limits depend on whether you use a regular Gmail account or a paid Google Workspace account. Count with it that you might run out of quota.
2. Better approach is to just save the data and process them asynchronously. Look at a Google spreadsheet as an inbound queue. Each row is a message you insert into the queue. Use then triggers which can run another Google Apps Script function to process the newly inserted rows and there you can do what you want - you can send an email, create a Google Document, create a Google Spreadsheet, make a request, save the data as a Google Drive file...
3. The get/post handlers cannot modify response headers. Like in the demo I do return back JSON indicating the status. In the client check for HTTP 200 and then addionaly for the status. And handle all other errors.
4. Once you authorize the script the authorization token will be in use for some time. But it will eventually expire, typically I see it is happening once a year. Then you will start getting HTTP 401 or HTTP 400 from the script itself. All you need to do is to open the script and run it manually and re-authozie it again.

Reliability

I have been using Google Apps Sctipt web-apps for many years without any issues.

Security

Each script has a unique URL. The security is based on the "unguessable" URL. You can further restrict the web app only to Google Workspace domain or your account.

Leave a Comment

I hope this will help you to create even more interesting automations using the Automate app and Google Apps Script.

Feel free to leave a comment how you use the integration in your flows.

Happy automating!

Sample Web App Code

// GET method handler
// GET method is not supported in the demo
// use the doGet function to retrieve data from Google Spreadsheets or other services using Google Apps Script
function doGet(request) {
  return http400();
}

// POST method handler
function doPost(request) {
    try {
        // check logs sheets to see what is in the request variable
        log(JSON.stringify(request));

        if (request !== undefined && request.postData !== undefined && request.postData.contents !== undefined) {

            var json = JSON.parse(request.postData.contents);

            // insert data
            if (json.lat && json.lon && json.value) {
                insertData(json.lat, json.lon, json.value);
                return http200();
            }
        }

    } catch (e) {
        return http400(e.message);
    }

    return http400();
}

// make sure the "data" sheet exists in the spreadsheet
function insertData(lat, lon, value) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data');
  const values = [];
  values.push(getTimestamp());
  values.push(lat);
  values.push(lon);
  values.push(value);
  values.push('=HYPERLINK("http://maps.google.com/maps?q=' + lat + ',' + lon + '"; "Show")');
  sheet.getRange(sheet.getLastRow() + 1, 1, 1, values.length).setValues([values]);
}

function http200() {
  return ContentService.createTextOutput(JSON.stringify({"status":200})).setMimeType(ContentService.MimeType.JSON);
}

function http400(message) {
  if (message) {
    return ContentService.createTextOutput(JSON.stringify({"status":400, "message": message})).setMimeType(ContentService.MimeType.JSON);
  } else {
    return ContentService.createTextOutput(JSON.stringify({"status":400})).setMimeType(ContentService.MimeType.JSON);
  }
}

function getTimestamp() {
    return Utilities.formatDate(new Date(), 'UTC', 'yyyy.MM.dd HH:mm:ss');
}

// make sure the "logs" sheet exists in the spreadsheet
function log(message) {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('logs');
    const values = [];
    values.push(getTimestamp());
    values.push(message);
    sheet.getRange(sheet.getLastRow() + 1, 1, 1, values.length).setValues([values]);
  } catch (e) {};
}