Call AppSheet API from Apps Script

dbaum · 07-26-2022 08:21 PM

Here's a script template to invoke an AppSheet action via API from an Apps Script. It could surely be streamlined or optimized in various ways. I find this scaffolded structure of building variables upon variables to be helpful in development, troubleshooting, and script reuse (of course, it could be turned into a function with some/all of the variables as function parameters). It includes logging of both the request and the response, which also helps in troubleshooting.

  // Values specific to app
  const appAccessKey = 'my-appAccessKey';
  const appId = 'my-appId';

  // Values specific to individual AppSheet API call
  const table = 'my-Table';
  const action = 'my-Action';
  const userSettings = {'my-ColumnName': my-ColumnValue, 'my-Column2Name': my-Column2Value};
  const properties = {
   'Locale': 'my-LocaleCode',
   'RunAsUserEmail': my-UserEmail,
   'UserSettings': userSettings
  };
  const rows = my-Array;
  
  const body = {
    'Action': action,
    'Properties': properties,
    'Rows': rows
  };
  const payload = JSON.stringify(body);

  // Values universal to AppSheet API calls
  const url = 'https://api.appsheet.com/api/v2/apps/' + appId + '/tables/' + table + '/Action';
  const method = 'post';
  const headers = {'ApplicationAccessKey': appAccessKey};

  const params = {
    'method': method,
    'contentType': 'application/json',
    'headers': headers,
    'payload': payload, 
    'muteHttpExceptions': true
  };

  const requestSimulate = UrlFetchApp.getRequest(url, params);
  let response
  try{
    response = UrlFetchApp.fetch(url, params);
  }
  catch(err){
    Logger.log('err: ' + err);
  }
  finally{
    Logger.log ('requestSimulate:');
    Logger.log (requestSimulate);
    Logger.log ('response: ' + response);
  }

Luke_H

Hey @dbaum you are da bomb! I'm gonna try this out. Thanks!

Harriswe

Hi @dbaum in the my-Array is that passing an array of records to the AppSheet API call or a single record made up of array of items? I.e. something like the example below, which would be represent a single record?

 "Rows": [
    {
      "FirstName": "Jan",
      "LastName": "Jones",
      "Age": 33,
      "Department": "Accounting",
      "Street": "110 Beach Blvd",
      "City": "Newport Beach"
  }
]

I ask because I am using GAS to parse through a JSON object which represents an array or records returned from a 3rd party API call, since AppSheet does not support this 'type' of returned value in Automation. So the leg-work has to be done in GAS by invoking the AppSheet web-hook.

dbaum

It's an array comprising one or more objects (records), each comprising one or more key-value (column-value) pairs. See AppSheet API spec in Invoke the API - AppSheet Help and associated articles.

Ju213

@dbaum I tried same code and getting 200 response in return but rows aren't getting updated. In audit log it was mentioned 'Locale': 'my-LocaleCode' as incorrect . Can you please help here?

lui_meta

try 'en-US'