Why update a linked record?
Sometimes you may need to update a linked record in a linked table, an example use case:
- if you have a table of Books to track (this could also be assets or some inventory of some sort),
- and you need to allow people to check out a book and check them back in, and track the checkouts/checkins.
You can create a second table to track Checkouts, and a third table to track Checkins, and then link the tables so that each checkout or checkin is tied to a specific book. You can create separate forms to let people select an available book to checkout, or select an unavailable book to check back in.
You can keep track of the status of whether the book is currently checked-out using a checkbox column in the Books table, and you'll want to update that status whenever the book has been checked out or in.
How to update a linked record
We do not have a native bot action for updating linked records yet; however, you can do this using our Apps Script bot action and the sample code below!
Here's a video from a user who set up this example and demonstrates the Apps Script bot action working to update the "checked out" status in the Books table:
Update linked record with Apps Script bot action
1. Setting up the tables
To leverage this type of "update linked record" scenario, you need to have a table relationship established with some Lookup columns (see help center articles here).
- Think about the relationship between the tables of data. It's often easier to think of it as a "parent-child" relationship, for example:
- "projects" have "tasks",
- or "cars" have "engines",
- or "teachers" have "students"
- or "books" have "readers"
- Sometimes relationships are one-to-many (one project has many tasks, but a task doesn't have multiple projects), and sometimes they are many-to-many (each teacher has many students, and each student has many teachers).
- Create your tables and create a relationship from the "child" to the "parent" -- in the example, the parent is the "Books" and the individual "checkouts" and "checkins" are tied to each book -- you should have at least one Lookup column that will let you select and link records from the other table.
- In the parent table ("Books"), add a column of type "Metadata > Row ID", and make sure to have a second Lookup column that uses the same relationship to show the parent Row ID column (the book's row ID). The reason for this is that we will trigger the Apps Script from the child table, and we need the linked Row ID to pass along to Apps Script so it knows which row to update in the parent table.
Once you have your tables setup with the right relationships and columns, we can go over to Apps Script to quickly put together the script to update the linked row.
2. Setting up Apps Script function
- Open script.google.com to access the Apps Script project list.
- Create a new Apps Script project and give it a recognizable project name.
- Ensure you have the Tables API service enabled.
- Paste in the following code:
// Test that the script works correctly
function test_update_function() {
var tableId = 'a7tQp8VYl02dHbGsAPQk06'; // Replace with your table id
var recordId = '8BUmYOQ_Y-Y2bkgL6DL4nO'; // Replace with your record id
var columnName = 'Checked_out'; // Replace with your column name
var newValue = 'true'; // Replace with your value
update_related_table_field(recordId, tableId, columnName, newValue);
}// Apps script function to let us update a record in another table
// The user must have edit access to the target table
function update_related_table_field(recordId, tableId, columnName, newValue) {
Logger.log('parameters:', recordId, tableId, columnName, newValue);
// All parameters are passed as strings, so need to check if the
// "newValue" parameter is 'true' and then set as boolean true.
// This is specific to updating a "checkbox" type column, you should
// update this logic based on what you need for your use case.
// See what values are accepted in our documentation here:
// developers.google.com/apps-script/advanced/tables#create_a_row_in_a_table
var checkedOutValue = false;
if (newValue === 'true') {
checkedOutValue = true;
}// Each record in a table has a unique "name" that is represented
// via a URL-like string like so: tables/<tableId>/rows/<recordId>
// We need to build this name so we can tell Apps Script to update
// the right record in the right table.
var linkedRecordName = 'tables/' + tableId + "/rows/" + recordId;update_record(linkedRecordName, columnName, checkedOutValue);
}// Update the specified record with the new value in the target column
function update_record(recordName, columnName, newValue){
Logger.log('update:', recordName, columnName, newValue);// We need to pass along an object that specifies the column we want to
// update, and what the new value of the column is.
var updatedColumnValues = {};
updatedColumnValues[columnName] = newValue;// We call the Tables API using the Area120Tables Apps Script bean.
var response = Area120Tables.Tables.Rows.patch({values: updatedColumnValues}, recordName);
Logger.log("response:" + JSON.stringify(response)); // Log the response
}
** IMPORTANT NOTE: before setting up the bot action, make sure to click the "Run" toolbar button to authorize the script for permissions you need.
You can try running the script with the "test_update_function" (make sure to update the values in the function with your own table and record ids), to see it work first.
3. Setting up the bot action
Finally, in the child table, 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 following parameters (which you can see in the demo video as well):
- the linked row ID (use the column variable for the Lookup column {{colName}})
- the linked table ID (get from the browser URL: /table/<tableId>/...)
- the column name (case sensitive)
- the new value you want to update with
NOTE: the order of the function parameters you list in the bot matters, for example, if in the bot we put the table ID as the first parameter in the list, then in the Apps Script code, we would need to update the function definition so the order of the parameters also match (table ID first):
function update_related_table_field(tableId, recordId, columnName, newValue)
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.