Send all columns as function parameter when calling a script

[BreezyDan]

Hi All

Does anyone know if it's possible to send all columns as as single function parameter when using bots to call a script?

I have a basic CRM app. I'm using a bot to send adds and updates to an apps script that will then handle the data. Currently, I'm having to list every column individually as it's own function parameter and when I add new columns I have to update the bot,  the function parameters in the script and a variable in the script. 

function contactCreated(firstname,lastname,email...) {
  var data = {
    firstName: firstName,
    lastName: lastName,
    email: email
    ...
  }

  doSomething(data);
}

It would be great to be able to just set a single function parameter and be done.

function contactCreated(allRowData) {
  doSomething(allRowData);
}

If anyone knows if it's currently possible or has a decent workaround I'd be grateful 🙂

Cheers, Dan

[dbaum · Accepted solution]

In the automation task, send a list. Although you'd still need to explicitly include every column, it would be within a single parameter.

LIST([First Name], [Last Name], ...)

In your function, process the single parameter as an array. Since you define the sequence of array elements in AppSheet, you'll always know what to expect in your function. There's likely a way to elegantly iterate (e.g., using forEach) through the array to create a JavaScript object (maybe by including not just values but also keys in the list constructed in AppSheet). Here's the brute force way to illustrate the concept of parsing the array.

const firstName = allRowData[0];
const lastName = allRowData[1];

[dbaum · Accepted solution]

In the automation task, send a list. Although you'd still need to explicitly include every column, it would be within a single parameter.

LIST([First Name], [Last Name], ...)

In your function, process the single parameter as an array. Since you define the sequence of array elements in AppSheet, you'll always know what to expect in your function. There's likely a way to elegantly iterate (e.g., using forEach) through the array to create a JavaScript object (maybe by including not just values but also keys in the list constructed in AppSheet). Here's the brute force way to illustrate the concept of parsing the array.

const firstName = allRowData[0];
const lastName = allRowData[1];

[BreezyDan]

Thanks for taking the time to reply 🙂

Ill have a look at the List option but thinking out loud, I'm wondering if it is best to use them all as single parameters anyway?

I need to replace any null values with just an empty string. Bit of a noob question but performance wise, do you know if it would be better to do it as an IF() expression in AppSheet before I call the script

IF([Last Name] = null,"",[First Name])

or send the unchanged values from AppSheet and worry about null values in the script?

if(lastName == null) lastName = "";

note: I don't need/want them as required columns in AppSheet

[dbaum · Accepted solution]

---

@BreezyDan wrote:

I'm wondering if it is best to use them all as single parameters anyway

---

It's presumably a matter of preference--balancing considerations like script complexity and effort to update when you add/remove columns. The most inherently extensible and robust approach is likely to use a single list-based array parameter that includes both keys and values, which the function immediately transforms to an object and then processes the object's entries from there. With that approach, the only changes you'd have to make when you add a column would be to add the key/value pair to the AppSheet list expression and then add to your function whatever processing you need of the additional entry that would be included automatically in the object by virtue of being included in the function's array parameter.

---

@BreezyDan wrote:

performance wise, do you know if it would be better

---

I don't know which approach would be more performant. My assumptions are that Apps Script is likely inherently faster than AppSheet as well as that any difference in your scenario is likely negligible.

---

@BreezyDan wrote:

[Last Name] = null

---

ICYMI, that's not AppSheet expression language. You likely mean:

ISBLANK([Last Name])

[BreezyDan]

Thank you for your input, much appreciated 🙂

[spotlightcanada]

Here is what I did to pass an entire row of data as a parameter into a script call.  It has the same downside as above, as you need to know all the column names, but at least on the script side of things you don't need to access the data by index.

I used Copilot to write this for me anyway, all I did was pasted in the column names.  So this shouldn't be THAT hard to maintain if your table changes.

Here is the formula for a function parmater:

CONCATENATE(
  "{",
  '"COL_1":"', [COL_1], '",',
  '"COL_2":"', [COL_2], '",',
  '"COL_3":"', [COL_3], '",',
  "}"
)

In the receiving script, just parse the data back into JSON:

data = JSON.parse(data);