Create Array for Function parameter

[AI-FL]

Looking for guidance as I'm new to calling scripts in AppSheet. 

I have an Apps Script that creates a POST request with a required JSON array.  I would like to know if Appsheet can create the array variable using a loop on my returned list (below) OR if it has to be done in the Apps Script.

My Parts Used Table in Appsheet:

Part ID	Description	Part #	Price	Quantity
dbfb2ee7	O-RING 4 - Replacement part for 6E5-43864-00-00	63P-42643-01-00	25	4
cb6bc575	WATER PUMP REPAIR - Replacement part for 61A-W0078-A3-00	LFR-5A110-00-00	80	1

The above Part ID list (dbfb2ee7, cb6bc575)  is returned using:

SELECT(Parts Used[Part ID], ([_THISROW].[Job ID] = [Job ID]), TRUE)

I would like to create an array something like the one below to pass in as a Function Parameter on AppSheet.

lineArray = [
{ "Description": "Maintenance Services",
"SalesItemLineDetail": {
"Qty": 4,
"UnitPrice": 25.0,
"ItemRef": {
"name": "O-RING 4 - Replacement part for 6E5-43864-00-00",
}
},
"LineNum": 1,
"Amount": 100 ,
},
{ "Description": "Maintenance Services",
"SalesItemLineDetail": {
"Qty": 1,
"UnitPrice": 80.0,
"ItemRef": {
"name": "WATER PUMP REPAIR - Replacement part for 61A-W0078-A3-00",
}
},
"LineNum": 2,
"Amount": 80 ,
},
]

Thanks.

[scott192 · Accepted solution]

The way I am doing something similar is as follows.

I use a bot that constructs a JSON file from data in the App.  You can use all the template expressions to create records and arrays within this JSON.  It's a bit finicky at first to get all the { and } and [ and ] and <<Start:>> <<End>> things in the right place, but it's something you learn with practise.

The bot constructs the JSON and then saves that JSON with a unique filename (turn off the time part of the filename and use a key field to name the JSON).  You will get something like 8a453bd1.JSON as the filename.  The file is saved in a KNOWN place.  You will already have the folderid of that known place as part of your Appscript.

You will call your Appscript as part of the bot that constructs the JSON file.  You will pass the JSON filename as a parameter into your Appscript Task.  Your Appscript Task will look for that filename in the known folder and open it.  Then you can do whatever else you need to do with the JSON.

[AleksiAlkio]

The question is.. what do you want to do with it? What's the purpose?

[AI-FL]

The purpose is to create the array and pass to my Apps Script function via Appsheet Process.

[AleksiAlkio]

And what the script will do? Thinking what the goal is.

[AI-FL]

The script will take the array as a parameter and pass to the POST call.  For example:

    var response = UrlFetchApp.fetch(url, {

      method: 'POST',

      muteHttpExceptions : true,

      payload: JSON.stringify(arrayVariablefromAppSheet),

      headers: {

        Authorization: 'Bearer ' + service.getAccessToken(),

        'Content-Type': 'application/json',

        Accept: 'application/json'

         }

    });

[AleksiAlkio]

Still don't know what the goal is exactly, but yes you can do this with the Bot > Webhook using Start: & End expression with the json.

[scott192 · Accepted solution]

The way I am doing something similar is as follows.

I use a bot that constructs a JSON file from data in the App.  You can use all the template expressions to create records and arrays within this JSON.  It's a bit finicky at first to get all the { and } and [ and ] and <<Start:>> <<End>> things in the right place, but it's something you learn with practise.

The bot constructs the JSON and then saves that JSON with a unique filename (turn off the time part of the filename and use a key field to name the JSON).  You will get something like 8a453bd1.JSON as the filename.  The file is saved in a KNOWN place.  You will already have the folderid of that known place as part of your Appscript.

You will call your Appscript as part of the bot that constructs the JSON file.  You will pass the JSON filename as a parameter into your Appscript Task.  Your Appscript Task will look for that filename in the known folder and open it.  Then you can do whatever else you need to do with the JSON.

[AI-FL]

The bot is working now to create the JSON file.  Thanks for this.  One last question...you mention folderid.  Are you using a Google drive folder by chance to house the JSON files?  I'm getting stuck on having to point to the JSON file (8a453bd1.JSON) in a google drive, read it, then extract the file contents to payload variable.  Did you mean pass the filename as parameter in Appsheet task?  Can you provide example?

[scott192 · Accepted solution]

Sorry for the delay...was on holiday!

So, whenever Appsheet saves a file it places it in a folder within your App's working directory.  In my case the working directory is on a Shared Drive which is the recommended way of doing things in Google Workspace.

This is how my JSON creator bot is configured.  The first time this executes, it creates the JSONQuotes folder within the App Folder.  You can then open this folder and retrieve the FolderID from Google Drive.  Armed with this you can use something like the following to query the folder and get the file...

const jsonfilestore = "TheFolderIDoftheJSONQuotesfolder";

function generateJSONQuote(JSONFilename) {
  var files = DriveApp.getFolderById(jsonfilestore).getFilesByName(JSONFilename);
  if (files.hasNext()) {
    var file = files.next();
    var content = file.getBlob().getDataAsString();
    var json = JSON.parse(content);
    //Logger.log(JSON.stringify(json, null, 2));
    file.setTrashed(true);

content === the json file as created by the bot
json === converted to javascript object for stepping through and reading etc.

Hope that helps!

[AI-FL]

Thanks @scott192 .  I'm going to give this a try.  Definitely looks like it will solve my loop-thru-list problem.