Re: Error when calling AppSheet API from Google Ap...

DannyD_PNG · 07-25-2022 08:48 PM

Hi Community,

I am trying to call the AppSheet API from a Google Apps Script, but I am getting an error.

This is the code in my Google Apps Script:

function testApi(){
let appId = "1234";
var url = `https://api.appsheet.com/api/v2/apps/${appId}/tables/transactions/Action`;
var options = {
"method": "post",
"headers": {
"applicationAccessKey": "abcd"
},
"muteHttpExceptions": false,
"payload": {
"Action": "Find",
"Properties": {
"Locale": "en-US",
"Location": "34.850042, -80.7325279",
"Timezone": "Estearn Standard Time",
"UserSettings": {
"Option 1": "value1",
"Option 2": "value2"
}
},
"Rows": []
}
};
// options = {muteHttpExceptions: true};
var response = UrlFetchApp.fetch(url, options);
console.log(response.getResponseCode())
}

This is the error I receive:

Audit Log Details
Action Details
Email Details
REST API:
{
"Action": "Find",
"Properties": {},
"Rows": []
}

Properties:
{
"RestAPIVersion": 2,
"TableName": "transactions",
"AppTemplateVersion": "1.000022",
"Action": "Find",
"Errors": "API 'Properties' does not have unexpected Type of 'JObject'. Value is: '{\r\n \"Action\": \"Find\",\r\n \"Properties\": \"{Location=34.850042, -80.7325279, Locale=en-US, Timezone=Estearn Standard Time, UserSettings={Option 1=value1, Option 2=value2}}\",\r\n \"Rows\": \"[Ljava.lang.Object;@75b5622e\"\r\n}'",
"AppTemplateName": "483b1d1f-de86-4005-be86-1c86584ce72c",
"Operation": "REST API invoke",
"RecordType": "Start",
"Result": "Failure"
}

I am grateful for any help on this issue.

dbaum

There may be other issues, but the Audit Log's error message says the your API call's Properties can't be parsed as provided. At a minimum, it looks like you misspelled "Eastern". Also, the UserSettings you included look generic--potentially just copied verbatim from Invoke the API - AppSheet Help and not actually applicable to your app.

DannyD_PNG

Thanks. I corrected the misspelling of "Eastern". Unfortunately, I still get the same error. I did reference the Invoke the API - AppSheet Help to write my code, but I think I modified the code to fit my app. In my original post, where I have "1234" and "abcd" for my appId and my applicationAccessKey, I have entered the true, full Ids. Also, I edited the url, so that it references the table, "Transactions", which I want to access in my spreadsheet.

Are there other parts of the code I need to modify to be applicable to my app?

dbaum

"Also, the UserSettings you included look generic--potentially just copied verbatim from Invoke the API - AppSheet Help and not actually applicable to your app."

If you don't need those UserSettings properties, delete them. If your app uses different keys or values for user settings you need to reference in the API call, modify them in your script.

Check your overall syntax--I often find that I'm missing a comma or closing brace that throws everything off. In constructing an AppSheet API call from GAS, I find it easier to compile the overallUrlFetchApp.fetch statement from granular variables.

DannyD_PNG

Hi. Thanks, again, for your help.

I removed the unnecessary UserSettings in my code. Unfortunatley that didn't help.

I don't think I have any different keys or values in my User Settings. I have not changed anything in my User Settings.

Here is what my code currently looks like:

function testApi5(){
let appId = "1234";
var url = `https://api.appsheet.com/api/v2/apps/${appId}/tables/transactions/Action`;
var options = {
"method": "post",
"headers": {
"applicationAccessKey": "abcd"
},
"payload": {
"Action": "Find",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": []
}
};
var response = UrlFetchApp.fetch(url, options);
console.log(response.getResponseCode())
console.log(response.getContent())
}

This is the response I get in the GAS Execution Log:

12:19:27 PM Info 200
12:19:27 PM Info []

This is what I get in the Audit Log Details:

REST API:
{
"Action": "Find",
"Properties": {},
"Rows": []
}

Properties:
{
"RestAPIVersion": 2,
"TableName": "transactions",
"AppTemplateVersion": "1.000023",
"Action": "Find",
"Errors": "API 'Properties' does not have unexpected Type of 'JObject'. Value is: '{\r\n \"Action\": \"Find\",\r\n \"Properties\": \"{Locale=en-US, Location=47.623098, -122.330184, Timezone=Pacific Standard Time}\",\r\n \"Rows\": \"[Ljava.lang.Object;@58937e4\"\r\n}'",
"AppTemplateName": "[Removed, in case it is sensitive information]",
"Operation": "REST API invoke",
"RecordType": "Start",
"Result": "Failure"
}

I have combed through the code. I can't find any missing commas or braces.

Thank you, in advance, for any further suggestions you might have.

Marc_Dillon

Typo in "Eastern"?

DannyD_PNG

Hi. Thanks for this suggestion. Unfortunately, I still get the same error after correcting the misspelling of "Eastern".

DannyD_PNG

Hi @dbaum. Thanks, again, for your help.

I removed the unnecessary UserSettings in my code. Unfortunately that didn't help.

I don't think I have any different keys or values in my User Settings. I have not changed anything in my User Settings. This is what they look like:

Here is what my code currently looks like:

function testApi5(){
let appId = "1234";
var url = `https://api.appsheet.com/api/v2/apps/${appId}/tables/transactions/Action`;
var options = {
"method": "post",
"headers": {
"applicationAccessKey": "abcd"
},
"payload": {
"Action": "Find",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": []
}
};
var response = UrlFetchApp.fetch(url, options);
console.log(response.getResponseCode())
console.log(response.getContent())
}

This is the response I get in the GAS Execution Log:

12:19:27 PM Info 200
12:19:27 PM Info []

This is what I get in the Audit Log Details:

REST API:
{
"Action": "Find",
"Properties": {},
"Rows": []
}

Properties:
{
"RestAPIVersion": 2,
"TableName": "transactions",
"AppTemplateVersion": "1.000023",
"Action": "Find",
"Errors": "API 'Properties' does not have unexpected Type of 'JObject'. Value is: '{\r\n \"Action\": \"Find\",\r\n \"Properties\": \"{Locale=en-US, Location=47.623098, -122.330184, Timezone=Pacific Standard Time}\",\r\n \"Rows\": \"[Ljava.lang.Object;@58937e4\"\r\n}'",
"AppTemplateName": "[Removed, in case it is sensitive information]",
"Operation": "REST API invoke",
"RecordType": "Start",
"Result": "Failure"
}

I have combed through the code. I can't find any missing commas or braces.

Thank you, in advance, for any further suggestions you might have.

DannyD_PNG

I apologize for the multiply replies. When I make a reply, then the reply sometimes 'disappears'. So, I created a new reply.

Marc_Dillon

I think you need to stringify the payload.

DannyD_PNG

Thanks, @Marc_Dillon. That seems to have moved me forward. However, now I am receiving a different error: 'Action' is missing.

This is my new code:

function testApi7(){
let appId = "1234";
var url = `https://api.appsheet.com/api/v2/apps/${appId}/tables/transactions/Action`;

var payload = {
"Action": "Find",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": []
};

var options = {
"method": "post",
"headers": {
"applicationAccessKey": "abcd"
},
"payload": JSON.stringify(payload)
};

var response = UrlFetchApp.fetch(url, options);
console.log(response.getResponseCode())
console.log(response.getContent())
}

This is the Audit Log Detail:

REST API:
{
"Action": "'Action' is missing.",
"Properties": {},
"Rows": []
}

Properties:
{
"RestAPIVersion": 2,
"TableName": "transactions",
"AppTemplateVersion": "1.000023",
"Errors": "'Action' is missing.",
"AppTemplateName": "483b1d1f-de86-4005-be86-1c86584ce72c",
"Operation": "REST API invoke",
"RecordType": "Start",
"Result": "Failure"
}

I did a Google search for: "appsheet Errors: 'Action' is missing", but I could not find a relevant solution.

dbaum

Here's a script that I use successfully. One difference I see is that I include the contentType parameter.

  // 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 = 'Add';
  const userSettings = {'my-ColumnName': my-ColumnValue};
  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);
  }

DannyD_PNG

@Marc_Dillon and @dbaum, thank you both very much! Each of you contributed to the solution. As @Marc_Dillon suggested, I needed to stringify the payload, and as @dbaum suggested, I needed to include the contentType parameter.

Is there a way I can accept each of your posts as the solution?

mike25

Hello - I am trying to get the code posted in this thread to work and I am having issues....can you take a look at this code and let me know if you see anything wrong?

function testAPI3(){
  // Values specific to app
  const appAccessKey = '12345';
  const appId = '12345';

  // Values specific to individual AppSheet API call
  const table = 'BidVersion';
  const action = 'Add';
  const userSettings = {};
  const properties = {
   'Locale': 'en-US',
   'RunAsUserEmail': 'mike@abc.com',
   'UserSettings': userSettings
  };
  const rows = {
        //"_RowNumber": "9",
        "Row ID": "8",
        "Deal ID": "114",
        "Bid ID": "2000",
        "Bid Version": "1",
        "Bidding Stage ID": "14",
        "Bid Type": "ROM",
        "Project Type": "",
        "Construction Type": "",
        "Contract Type": "Fixed Price",
        "Bid Deadline": "12/29/2023 20:06:08",
        "Project Address": "",
        "Bidding Notes": "Test Notes",
        "BidTo Name": "Joe Blow",
        "BidTo Address": "1234 Main Street",
        "BidTo Email": "a@a.com",
        "Bid Folder ID": "",
        "Bid Folder URL": "",
        "Approved By": "",
        "Amount Total": "",
        "Amount GC": "",
        "Amount OHP": "",
        "Date ITB Created": "12/20/2023 10:40:24",
        "Date Requested": "12/20/2023 20:06:48",
        "Date Approved": "12/20/2023 14:57:17",
        "Date Completed": "12/20/2023 14:55:49",
        "Sync": "1",
        "Sync Fields": "bidding stage, date completed",
        "Deal": "_Test Deal [2000 v1]",
        "Bid": "[2000 v1]"
    };
  
  const body = {
    'Action': action,
    'Properties': properties,
    'Rows': rows
  };
  const payload = JSON.stringify(body);
  Logger.log(payload)

  // 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);
  }
}

Error when calling AppSheet API from Google Apps Script