We do not support a native way to sync changes in Sheets back to Tables at this time, although you can use our Apps Script service to achieve something similar. On the other hand, you can set up a 1-way sync from Tables to Sheets using the IMPORTDATA() function.
In this article, we have a basic example of how to use Apps Script to add rows that are created in Sheets into Tables.
|
Will do |
Won't do |
|---|---|
|
If you fill out three columns in a row in Sheets, it will add a row to Tables with those values |
|
Getting started:
1. Tables: Create a new table and copy ID
- Visit tables.new — This creates a new workspace with 1 table in it
- Note the table ID from the end of the URL (You'll copy and paste this into apps script later)
2. Sheets: Create a new spreadsheet and connect to Apps Script
- Visit sheets.new
- Go to Extensions → Apps Script — This creates a bound script. This will allow you to set up Apps Script triggers on events in your spreadsheet
3. Apps Script
- That will open up your script in the Apps Script editor
- Click + next to the Services section of the left sidebar
- Find Area120 Tables API service and click Add.
- Replace the script code with the below code
- Replace TablesID with your table ID (from Step 1)
// This function runs with the Apps Script Trigger
function edited(e) {
var theRow = e.range.getRow();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(theRow, 1, 1, 3)
var rangeArray = range.getValues();
var rangeString = JSON.stringify(rangeArray);
// Only run if the entire row is filled out
if (rangeArray[0][0] != "" && rangeArray[0][1] != "" && rangeArray[0][2] != "") {
makeRow({
"Untitled Column 1": rangeArray[0][0],
"Untitled Column 2": rangeArray[0][1],
"Untitled Column 3": rangeArray[0][2]
})
}
}
function makeRow(rowValues) {
// TablesID uses the Table ID, not the workspace ID.
var tablesID = 'xxxxxxxxxxxxxxxxx';
// This prepends "tables/" before the ID for the create call
var tableName = "tables/" + tablesID;
// Write to the table
// rowValues is an object created from the Sheets row in the edited function below
Area120Tables.Tables.Rows.create({values: rowValues}, tableName);
}
4. Set up Apps Script Trigger
- Open the script's Project Triggers
- Click Add Trigger (in bottom right)
- Set up the trigger to run the edited function • From spreadsheet • On edit
Try it out! Open your Sheet and Table side-by-side
When you change a value in Sheets, it will check the row to see if the other columns are complete.
- In Sheets, if all 3 columns in the row are filled → Add a row to Tables
- If another column is empty → Do nothing