Batch-Wise Automation to Transfer Data Between Tables Based on Project ID

[Harsha007]

Hi Everyone,

I am working on an App Sheet use case where I need to automate data transfer between tables in batches. I have three tables:

1. Raw Records (X Table) – This table contains raw data with columns like Team Group (Enum) and Business Unit (Enum List).

2. Project Index (Y Table) – This table generates Project ID based on the selected Team Group and Business Unit from Raw Records.

3. Project Storage (Z Table) – This table should store project-wise data transferred from Raw Records.

What I Have Done So Far:

I created a virtual column in Raw Records to map the Project ID from Project Index.

I set up a bot using "For Each Row In Table", but it processes one record at a time, making it inefficient for bulk data transfer.

I explored Data Change Actions, but I'm unsure how to optimize it for batch processing.

Requirement:

Move data from Raw Records to Project Storage based on Project ID.

Process data in batches rather than row-by-row for better efficiency.

Only use App Sheet Automation (No App Script or Cloud Functions).

Questions:

1. How can I efficiently batch-process data using App Sheet automation?

2. Since I already mapped Project ID in Raw Records, how can I use this to optimize the automation?

3. Is there a better way to avoid row-by-row processing and improve performance?

Any suggestions or best practices would be greatly appreciated!

Thanks in advance!

[AlexM]

Hi,

Look into this
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Looping-with-actions/m-p/223708

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Looping-with-Actions-Sample-App-BUILD-Video/m-p/...

I think looping with actions is what you are after

The core of it is that you have a physical column that stores the IDs for the records you need in order to generate the new records in the other table.

Then using INDEX(...,1) you always:
  pick the 1st one in that list
  act on it
  delete it 
  the 2nd one becomes the 1st one and you start all over until that list is empty