Google App Script - Appsheet API - write data to Spreadsheet \ Add fields name to row 1

[Koichi_Tsuji]

I m now trying to dump App data to Google Spreadsheet, using Google App Script + Appsheet API.

This is code.

function appsheetApiGet() {

var appId = “YOURAPPID”
var accessKey = “YOURACCESSKEY”
var tableName = “YOURTABLENAME”

var url =
“https://api.appsheet.com/api/v2/apps/” + appId + “/tables/” + tableName + “/Action?applicationAccessKey=” + accessKey

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

var options =
{
“method” : “post”,
“contentType” : “application/json”,
“muteHttpExceptions” : true,
“payload” : JSON.stringify(payload)
};

var response = UrlFetchApp.fetch(url, options);

var json = response.getContentText();

var data = JSON.parse(json)

Logger.log(data)

var sheet = SpreadsheetApp.getActiveSheet()

sheet.clear()

for (var i = 0 ; i<data.length ; i++) {

  var row = [data[i]["Col1"],data[i]["Col2"]]
     
  sheet.appendRow(row)

}

}

It is working as expected apart from one missing thing, i.e. HOW TO ADD THE FIELDS NAME ONTO ROW #1

I dont have coding background with me, much appreciate for help from those who are familiar with App Script!

I assume I need to add spript to the part of

for (var i = 0 ; i<data.length ; i++) {

  var row = [data[i]["Col1"],data[i]["Col2"]]
     
  sheet.appendRow(row)

}

…

to add table/slice field name to row 1 but… Currently this code add only data values.

[LeventK]

@tsuji_koichi
getContentText() returns the headers as an Object. Haven’t tried but I believe use of .keys method for the UrlFetchApp call’s response might be needed to return those objects as a string array. May I humbly ask what’s your aim? Also I was not aware of a Find parameter as an action in AppSheet API as well

[Koichi_Tsuji]

One of the app is using Google Spreadsheet, and client asking to access to the whole tables. Google Sheet expression, importrange() can extract the data directly, but what they need to get the virtual fields value as well as sliced table to extract.
So I m now ending up with scripting…

[LeventK]

You can just copy the column headers from the sheet and then you can use a <<Start: ...>><<End>> expression in the payload with selecting the data as per slice’s row filter condition and record the data to this sheet with Sheet v4 API.

[Koichi_Tsuji]

Yes, I m currently doing that, but I m now trying to manage all in a single Google App script…

[LeventK]

It’s doable, why not? What makes you think that it can be done with separate scripts?

[Koichi_Tsuji]

Im afraid I m not fully catching up with your point.
Basically, what I want to do is to dump whole table/slice data to spreadsheet with filed name as header (row no 1)

You mean we do this jobs through seperate GAS ?

[Suvrutt_Gurjar]

Hi @tsuji_koichi,

I am not sure whether this variable can be negative and not tested but you may wish to try with

for (var i = -1 ; i<data.length ; i++) {

  var row = [data[i]["Col1"],data[i]["Col2"]]
     
  sheet.appendRow(row)

}

[Koichi_Tsuji]

Thank you @Suvrutt_Gurjar, but unfortunately changing to -1 is just collapsing the code. Cant run the script with error…

[Suvrutt_Gurjar]

Hi @tsuji_koichi,

Got it, It was a too naive suggestion on my part. My sincere apologies.

Just in case you wish to implement this purely through Google app script as say daily back up through say daily time based trigger, I am willing to assist further.

[Koichi_Tsuji]

Thank you for your help as always.

I need to construct manually set of the data filed name on the script, but the slight amendment to the code is now returning the expected result.

var sheet = SpreadsheetApp.getActive().getSheetByName(‘Project’)

sheet.clear()

var columnName = [“col1”, “col2” ,“col3”]

sheet.appendRow(columnName)

for (var i = 0 ; i<data.length ; i++) {

  var row = [data[i]["col1"],data[i]["col2"],data[i]["col3"]]

 sheet.appendRow(row)

}

}

Before going to loop, add (append) the field name manually, then in turn goes to the loop.

However, when I try to import large set of rows of table, like thousands of rows, App script returns error of timeout. Now I just submit a request for a help to support@appsheet.com

I suspect my code is possibly inefficident, tons of rooms for improvement, but this is all the best I come up with…

[Suvrutt_Gurjar]

Thank you @tsuji_koichi for sharing the revised code.

All the best.

[LeventK]

We can even do it with a webhook workflow my dear @tsuji_koichi I will discuss it with you tonight thru PM or WhatsApp. We can write it to a post when we come to a clean solution. Deal?

[Koichi_Tsuji]

Yes I

My client is G Suite Enterprise user. So they claim they really want to import app data instantly to their google spread sheet. So I wish to avoid coding as much as possible to stay completely No Code solution , but in this case it seems I can not avoid scripting. I tested code with different data table. Small numbers of records, working fine. Large data set. Timeout… I submitted a support request . Once there is progress is made, I get back here to share my learnings, thank you for your help and support friend!

[LeventK]

You’re very welcome @tsuji_koichi, it’s my pleasure. I will ping you tomorrow (July 29th) between 3-5pm JST (which will be 9-11am GMT+3 for me). Is that convenient for you?

[Koichi_Tsuji]

Will send you private message shortly to organize my friend .

[filipealvesferr]

I’m following to understand.

[Koichi_Tsuji]

I contacted support of Appsheet and they managed to release a fix to solve “timeout” issue, now Appsheet API is working fine to extract data through API after I twisted script a bit.