Call an Appscript function that returns a list, create rows in a table from the returned values

[Robert_Sherwood]

Apologies this amazing community for asking such a common question, but many of the forum posts related seem to have dead links and other issues, so I thought I would at least try to ask here. I will gratefully accept any pointers.

Here is what I am trying to accomplish:

1. We define a record in our App for a customer activity. (Add a row in a table)
2. This triggers an automation (Automation 1) which calls an apps script.
   1. The script creates a folder in a shared Google Drive for the files related to that activity, and returns the folder ID that it created.
   2. The automation adds the folder ID to the customer activity record (populates a column in the row)
3. The customer provides some files to us (out of band) which we upload to the folder created in step 2.
4. We change a column in the customer activity record (from a drop down)
5. This triggers another automation (Automation 2), which calls. another apps Script:
   1. this script accepts the folder ID for the customer activity as input
   2. it Identifies (recursively) all files in the folder.
   3. it ruturns an Array of strings of the form “GOOGLE_FILE_ID:FILE_NAME”.

EVERYTHING IS ALREADY WORKING UP TO THIS POINT. The rest is what I can’t figure out.

6. The next step in the should accept the List as input and creates one row in a different table for each item in the list:
   1. The cells to add to the row are:
      1. Customer Activity ID (taken from the row that triggers the automation)
      2. Google File ID ( Take the row, split by “:” and take the first item from the split)
      3. File Name (Take the row, split by “:” and take the second item from the split

I’ve tried defining a process step after running step 5 in automation 2,

Type: “Run a Data Action”

“Add new Rows”

Add row to this table “ActivityFiles”

With these values:

CustomerActivityID = [Row ID] (from the calling table)

FileName = - <> is what I’m trying to figure out

INDEX(
  SPLIT(
    INDEX([Get files in Affiliate Submission Directory].[Output], <<RowID>>),
    ":"
  ),
  1
)

GoogleDriveID =

INDEX(
  SPLIT(
    INDEX([Get files in Affiliate Submission Directory].[Output], <<RowID>>),
    ":"
  ),
  2
)

I know the syntax of that query is incorrect, but I don’t know how to refer to a particular item in a list. Should I use [_THIS]? Am I approaching this in a completely wrong way?

I’m not sure where to pass my list and how to refer to it once I get it there..

[WillowMobileSys]

In case you are not already aware, there is an AppSheet Google Cloud feature that seems like it will elimiante ALL of this.

It allows using a Google Drive folder as a datasource in your apps. You can Add and Delete files as you would with any other datasource and AppSheet will manage those request in the Google folder. Your app can reference the file table to link the files/images to other tables and, of course, you can use view capabilities straight from the app.

For more details, please refer to Use data from Google Drive

I hope this helps!!

[Suvrutt_Gurjar]

@Robert_Sherwood

The next step in the should accept the List as input and creates one row in a different table for each item in the list:

Please take a look at the several tips about adding rows to a table based on the enumlist in table 1.

I have myself used the following excellent tip by @Steve several times.

FAQ: add row per value in EnumList

Add one row per value in an EnumList column list. Try the sample app! For this example, the table from which the row-addition process will be initiated is called Control Table. The table to which new rows will be added is called Target Table. Use your own names as you see fit. Note that they could even be the same table. Control Table must allow updates; Target Table must allow adds. Control Table must have at least two columns for this process: EnumList (type EnumList; a list of Enum values;…

Add rows to another table WITH parameterized input

This pattern comes up more and more, so I thought I’d build a simple example. We already know that you can copy rows using the “add rows to another table” action. But sometimes we want our end user audience to provide some input first. The attached app shows a simple way to do this. At its most basic, the design has an “extra” table whose only real purpose is to allow data entry of a single row of info. This info could then be used when calling the “Copy” action. So… in our fictitious example…

[Robert_Sherwood]

Interesting! Based on playing with it, I see a couple of limitations, but I would love to know if I’m mistaken:

1. You can add a Directory, and you see the files and folder in that directory, but it doesn’t look like you can see files inside folders in a directory (e.g. “Folder/folder/file.txt”
2. You have to manually create the data source if you want to add it, so there’s no way to add a directory, and then make the new directory available as a data source.

These limitations seem very constraining, so I may be missing something fundamental - I guess the use case is like a very simple dropbox inside your app. Is that right?

[Robert_Sherwood]

I had seen these apps, but they both appear to copy data from one table to another, so if you have a table full of data, you can iterate through the rows, filter and transform them, and create new rows in another table.

Trying to access the example Apps gives me a lot of these:

So it’s tough to follow the logic of the processing.

The challenge I have is that the script that I call returns an Array, but there’s no table backing it - it’s all just an in memory data structure returned by an AppScript. I can refer to the entire array as [StepName].[Output], and I think I can convert the Array to a List with LIST([StepName].[Output]), and I could use INDEX() to step through it, but I haven’t seen a way to create a “Loop” to process the entries. Lots of posts saying “it can’t be done yet” from 2021, but It’s almost 2026 and I’m not sure if they’ve added that or if Sundar told everyone to stop fixing bugs or updating documentation and just add “Gemini” buttons everywhere instead

And a bunch of the URLs in threads are 404s, but at least there an “Ask AI” button everywhere

[Suvrutt_Gurjar]

@Robert_Sherwood

Trying to access the example Apps gives me a lot of these:

@Robert_Sherwood

So it’s tough to follow the logic of the processing.

I respect your views above. However, I am sorry that I may not entirely agree with your above experience with sample apps. My experience is good with most of the sample apps. I learned many AppSheet features in 2016 -18 time period by using sample apps and even today I refer those occasionally.

Especially the one I referred from Steve is still available and I could very much access it. Please see the screenshot below after I accesses it just now.

@Robert_Sherwood

but I haven’t seen a way to create a “Loop” to process the entries.

Yes, you are correct. There is no straightforward way to loop in AppSheet. The app by Steve does make use of looping with actions. There is even another sample app by exactly that name. Please refer below

Looping with actions

Kinda burnt out and just wanna get this out. I’ll try to circle back later and provide some documentation. https://www.appsheet.com/samples/Examples-of-loops-using-actions?appGuidString=7b9ae746-1f51-4ecb-8c39-b322e1c12da6

[Robert_Sherwood]

Thanks for the updated link to the app! I will look at it carefully.

[WillowMobileSys]

@Robert_Sherwood

You can add a Directory, and you see the files and folder in that directory, but it doesn’t look like you can see files inside folders in a directory (e.g. “Folder/folder/file.txt”

I am not sure what you mean by “see”. In the table datasource you do have the File column for documents and an Image column for pictures. I do not believe these contain folders but do contain File names. Google is now ID based when locating files and does need folder paths.

@Robert_Sherwood

You have to manually create the data source if you want to add it, so there’s no way to add a directory, and then make the new directory available as a data source.

Correct! To setup the datasource, you would need to know the folder location to configure the app.

And yes, these would be contraining IF what you are looking for is a way to dynamically manage file storage. Unfortunately, AppSheet was not designed for cloud file management so it is missing some important functions in that regard.

[WillowMobileSys]

@Suvrutt_Gurjar

@Robert_Sherwood

but I haven’t seen a way to create a “Loop” to process the entries.

Yes, you are correct. There is no straightforward way to loop in AppSheet. The app by Steve does make use of looping with actions. There is even another sample app by exactly that name. Please refer below

This looping with actions will require meta-data to help with processing of the array of values. in appSheet that meta-data comes from other table columns - which, as you pointed out, the return is NOT in a table.

So you have two options:

1. Have the script perform ALL the row processing - insertions and updates.
2. Insert the script return as a new row into a “utility” table setup with all of the columns needed to iterate over the array/list and perform the final app adjustments - data row adds and updates.

[Steve]

@Robert_Sherwood

You can add a Directory, and you see the files and folder in that directory, but it doesn’t look like you can see files inside folders in a directory (e.g. “Folder/folder/file.txt”

Correct: you can only see the immediate descendants of the directory, but cannot descend into or traverse it.

There is no built-in means to traverse or otherwise interact with a tree (directory or otherwise) in AppSheet.

[Markus_Malessa]

Two possible options:

1. Take the array (of objects in Appsheet data format?) i.e. [{Column:ColumnValue, OtherColumn:OtherColumnValue}] and set a task for a webhook to Add to your table and set the Rows object equal to your script return array.
2. Instead of returning the array from the Script, use GAS to implement the API and write the data as part of the script instead of a separate process in Appsheet.

[Robert_Sherwood]

Thanks to your demo app, and some elbow grease, I have something working. Since this community was so helpful, I’m going to try to strip out all of the customer specific stuff and publish a sample to benefit the next poor stiff who has to do this. Thanks so much!

[Robert_Sherwood]

Can’t thank you all enough: Here is the link to the template (hope this works) ImportFilesDemoApp - AppSheet

[Suvrutt_Gurjar]

Here you go with an excellent tip from @Kirk_Masden

How to share your app as a template / sample app via your portfolio and/or a URL

In this post, I’m revisiting topics that have been covered several times in “Tips” and other posts in this community. None of the information in this tip is new but I thought I’d glean the best information from the various posts that have been made so far and put it in one “tip” for easy reference. I’ve appended a set of links to previous posts that I have referred to in putting this together for those who might be interested. There are two ways in which one can share a sample app (now referred…

[Robert_Sherwood]

That was it. Thanks!