Can I batch create, update, or delete rows in a table?

Contents

• When would I want to batch create/update/delete rows in Tables?
• 1. Setting up the tables
• 2. Setting up Apps Script function
• 3. Setting up the bot action
• Debugging & troubleshooting

When would I want to batch create/update/delete rows in Tables?

Sometimes you may want to use Apps Script to modify a table in bulk:

• If you have a checklist where every quarter, you want to delete all items and replace the items.
• If you are syncing data between Tables and another app, like updating data from Sheets to Tables.

1. Setting up the table

This is a super easy, just create a new blank table with a few columns. That's all you need for now!

2. Setting up Apps Script function

1. Open script.google.com to access the Apps Script project list.
2. Create a new Apps Script project and give it a recognizable project name (so you can find it when selecting the script for your bot action later). 
   
   
    
3. Ensure you have the Tables API service enabled.
4. Then copy and paste any combination of the following code snippets below into your script to use: 
   NOTE: column names are case-sensitive, we will get the tableId and rowId from bot variables.

Batch Create

/** 
 * Creates multiple rows in a table
 * @param tableId - id of the table to create data in
 */
function addRows(tableId) {
  // Generate the "table name" which identifies the table to add rows to
  const tableName = "tables/" + tableId;
  if (!tableName) { console.error('TableId does not exist'); return; }

  // Batch add three rows to the blank table:
  // NOTE: you must replace the highlighted columnName
  Area120Tables.Tables.Rows.batchCreate({requests: [
    {row:{values:{"columnName1":"Test",  "columnName2":"Option A"}}},
    {row:{values:{"columnName1":"Test2",  "columnName2":"Option B"}}},
    {row:{values:{"columnName1":"Test3",  "columnName2":"Option B"}}},
  ]}, tableName);
}

Batch Update

/** 
 * Replaces and updates the column value for all rows in a table that match
 * @param tableId - id of the table to update
 * @param columnName - name of the column you want to update
 * @param fromValue - column value that you want to replace
 * @param toValue - value to replace "fromValue" in the column
 */
function updateRows(tableId, columnName, fromValue, toValue) {
  // Generate the "table name" which identifies the table to add rows to
  const tableName = "tables/" + tableId;
  if (!tableName) { console.error('TableId does not exist'); return; }

  // Get all the rows in the table where the column matches "fromValue"
  const response = Area120Tables.Tables.Rows.list(tableName);
  const rows = response.rows.filter(r => r.values[columnName] === fromValue);

  // Change the value for all the rows that matched to "toValue"
  // and then batch update the table
  const updateRequest = rows.map(r => (
    {row: {name: r.name, values: {[columnName]: toValue}}}
  ));
  Area120Tables.Tables.Rows.batchUpdate({requests: updateRequest}, tableName);
}

Batch Delete

/** 
 * Deletes all rows in a table
 * @param tableId - id of the table to delete rows in
 */
function deleteRows(tableId) {
  // Generate the "table name" which identifies the table to add rows to
  const tableName = "tables/" + tableId;
  if (!tableName) { console.error('TableId does not exist'); return; }

  // Get all the rows in the table (need to get the specific list of row ids)
  const response = Area120Tables.Tables.Rows.list(tableName);
  const deleteRowNames = response.rows.map(r => r.name);

  // Batch delete all rows in the table
  Area120Tables.Tables.Rows.batchDelete({names: deleteRowNames}, tableName);
}

 

** IMPORTANT NOTE: before setting up the bot action, make sure to click the "Run" toolbar button to authorize the script for permissions you need. 

3. Setting up the bot action

Finally, in the table we set up earlier, add a Bot to use the "Execute Apps Script function" bot action, and select and authorize to use the new script we've just created, and make sure to add the parameters, in the right order, to pass along the right information to the function (can use the variable [[table_id]] for tableId).

For example, if the script function we are using is multiply(), the function definition looks like:

  function addRows(tableId)

so the parameters we define in the bot must match:

1. table ID

NOTE: the order of the function parameters you list in the bot matters, and needs to match up with the function definition in the script.

Debugging & troubleshooting

If your bot isn't working like you expect, here are some steps to determine what might be going wrong:

Check that the bot is enabled and set with the correct triggers:

• For time-based: if no bot conditions/filters are set, then the bot may trigger for every row.
• For column-changed: if the trigger column is deleted/changes type, you'll need to reconfigure the trigger.

If using an "email"-type bot action:

• Make sure recipients are set correctly, they need to be valid email addresses, or Google accounts (via a Person-type column).
• Make sure email subject and body are valid and don't have incorrect use of variables.
• You can test that emails are being sent correctly by manually entering your own email address first.

If using an "add/update row"-type bot action:

• Make sure any values set are correct for the destination column type.
• Try "clearing" the value and re-setting the values that are not carrying through or may have been using variables from columns that were deleted or had column type changed.

If using an "execution Apps Script" bot action: 

• Make sure the correct script is selected and the correct parameters are passed in the correct order.
• Make sure the bot execution history to see if there were any errors or failure messages:
  
  
   
• Make sure the Apps Script execution history for errors and debugging messages:
  
• Try other common troubleshooting steps for Apps Script, and reach out to the larger public Apps Script community, which is a great resource for quick questions and answers from other community experts.