Sample App: Get a list of Files from Drive and add a record for each file

AppSheet Tips & Tricks
1

I really want to thank the community for their support with my annoying newbie questions:

See this thread ( Call an Appscript function that returns a list, create rows in a table from the returned values - #6 by Suvrutt_Gurjar )

I especially want to thank @Suvrutt_Gurjar and @Steve.

The problem was this:

  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”.

  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

There are two major challenges:

  1. It is not possible to directly process data returned from an external appscript. The value returned must be written into a data table.
  2. There is not a built in looping concept in AppSheets.

I have a sample app template here which solves these problems. I hope this will help anyone else who needs to solve this problem.

Import Files Demo App

6 Likes
2

Thank you @RS_Credentive. Appreciate your efforts in detailing your exact requirements, roadblocks you faced and sample app creation.

Such efforts add collective knowledge to the community knowledge pool.

@Jose_Arteaga : request you to move this post to the Tips and Tricks section.

4 Likes
3

Good call! and thank you all for being so supportive, I learn a lot from all of you!

3 Likes