Tables service

The Tables service allows scripts to programmatically read and edit rows within Google Tables.

Reference

For more information about this service, see the documentation for the Tables API. Like all advanced services in Apps Script, the Tables service uses the same objects, methods, and parameters as the public API. For more information, see How method signatures are determined.

To report issues and find other support, see the Tables support guide.

Sample code

Get a list of tables

The following sample shows how to get a list of all the tables the user owns.

//Getlistoftablestheuserowns
varresponse=Area120Tables.Tables.list();
if(response){
vartables=response.tables;
Logger.log(JSON.stringify(tables[0]));
}

Below is an example of the response, which includes information about the table and the table column definitions:

{
 "tables": [
 {
 "name": "tables/b6prMlkWyekbsCFeX6IOdu",
 "displayName": "Applicants"
 "columns": [
 {"id": "9qVCMvgh", "name": "Name", "dataType": "text"},
 {"id": "aD8dDXAS", "name": "Email", "dataType": "text"},
 {"id": "9pc0kdNX", "name": "Experience", "dataType": "tags_list",
 "labels": [
 {"id": "aAqi235Q", "name": "Android"},
 {"id": "bULZ4OK3", "name": "iOS"},
 ],
 },
 {"id": "8abYfCyo", "name": "Home Address", "dataType": "location"},
 {"id": "8ccERJ2v", "name": "Doc", "dataType": "file_attachment_list"},
 {"id": "aFb-tXf1", "name": "Stage", "dataType": "dropdown",
 "labels": [
 {"id": "8Hcb-Pxe", "name": "Applied"},
 {"id": "aM3EDGFf", "name": "Phone Screen"},
 {"id": "abyFLVKU", "name": "Onsite Interview"},
 ],
 },
 {"id": "9yKUThTi", "name": "Recruiter", "dataType": "person_list"},
 {"id": "a5c9WPVA", "name": "Interview Date", "dataType": "date"},
 {"id": "bqtbYPtH", "name": "Created", "dataType": "create_timestamp"},
 {"id": "bWR08pBv", "name": "Updated", "dataType": "update_timestamp"}
 ]
 },
 ... // more tables
 ]
}

The response includes up to 20 tables by default. To retrieve more tables, paginate the responses using the page_token and page_size parameters, shown below:

//Paginatethroughalistoftables
varpageSize=1000;
varpageToken;
varresponse=Area120Tables.Tables.list({page_size:pageSize});
while(response){
vartables=response.tables;
//getnextpageoftables
pageToken=response.nextPageToken;
if(!pageToken){
response=undefined;
}else{
response=Area120Tables.Tables.list(tableRequest,{page_size:pageSize,page_token:pageToken});
}
}

The max value of the page_size parameter for listing tables is 100.

Get a table’s information and column definitions

The following sample shows how to get a specific table’s information and column definition.

vartableID="TABLE_ID";//IDforthetable
vartableName="tables/"+tableID;
varresponse=Area120Tables.Tables.get(tableName);
Logger.log(JSON.stringify(response));

Find the table ID

To find a table’s ID, open the table in the Tables web app. In the URL at the top, the table ID is right after /table/.

The below sample shows where to find the table ID in various Tables URLs:

https://tables.area120.google.com/u/0/workspace/abcdefghijklmnop/table/TABLE_ID
https://tables.area120.google.com/u/0/table/TABLE_ID
https://tables.area120.google.com/u/0/table/TABLE_ID/view/abcedfghijk

Read rows of a table

The following sample shows how to get a list of a table’s rows and read the field values.

vartableID="TABLE_ID";//IDforthetable
varpageToken;
varpageSize=1000;
vartableName="tables/"+tableID;
varresponse=Area120Tables.Tables.Rows.list(tableName)
if(response){
for(vari=0,rows=response.rows;i < rows.length;i++){
if(!rows[i].values){//Ifblankrow,keepgoing
Logger.log("Empty row");
continue;
}
Logger.log(rows[i].values);
Logger.log(rows[i].values["Description"]);
}
}

A sample response is shown below. The response includes a list of the rows in the table and the values for each field.

{
"rows":[
{
"name":"tables/TABLE_ID/rows/a6tvEPska7l8rAlHlSdOLb",
"values":{
"Thing to do":"First item",// Text
"Size":100,// Number
"ETA":{"month":12,"day":3,"year":2021}// Date
"Stage":"Completed",// Dropdown
"Checklist":[// Checklist
"Do this",
"then this"
],
"Labels":[// Tags
"Green",
"Purple"
],
"Address":{// Location
"latitude":40.740726470947266,
"longitude":-74.00206756591797,
"address":"3014 Watson Lane, Sattler, TX 78130, USA"
},
"Archive?":true,// Checkbox
"ID#":1,// Auto ID
"Row creator":"liz@gmail.com",// Creator / Updater / Person
"Last updated":"October 7, 2020 6:30:38 PM EDT",
"Created on":"March 2, 2020 1:07:54 PM EST",
}
},
...// More rows
],
}

The response includes up to 50 rows by default. To retrieve more rows, paginate the responses using the page_token and page_size parameters, shown below:

varpageToken;
varpageSize=1000;
varresponse=Area120Tables.Tables.Rows.list(tableName,{page_size:pageSize});
while(response){
varrows=response.rows;
//readnextpageofrows
pageToken=response.nextPageToken;
if(!pageToken){
response=undefined;
}else{
response=Area120Tables.Tables.Rows.list(tableName,{page_size:pageSize,page_token:pageToken});
}
}

If there are more pages available, the response offers a nextPageToken. Otherwise, the response is undefined. To retrieve the next page of results, pass in the nextPageToken to the next list call.

The max value of the page_size parameter is 1,000.

Get one row from a table

The following sample shows how to read the field values of one row from a table.

vartableID="TABLE_ID";//IDforthetable
vartableName="tables/"+tableID;
varrowID="ROW_ID";//IDfortherowtofetch
varrowName=tableName+"/rows/"+rowID;//Constructrowname
varresponse=Area120Tables.Tables.Rows.get(rowName)
if(response){
Logger.log(response.values);
}

Filter the list of rows

To filter the list of rows in order to obtain only those results that you are interested in, use the filter parameter. For more details on the syntax and column types supported by the filter, please take a look at the filtering API documentation.

vartableID="TABLE_ID";//IDforthetable
varpageToken;
varpageSize=1000;
vartableName="tables/"+tableID;
varresponse=Area120Tables.Tables.Rows.list(tableName,{filter:"values.\"PointofContact\"=\"john.doe@gmail.com\""})
if(response){
for(vari=0,rows=response.rows;i < rows.length;i++){
if(!rows[i].values){//Ifblankrow,keepgoing
Logger.log("Empty row");
continue;
}
Logger.log(rows[i].values);
Logger.log(rows[i].values["Description"]);
}
}

The response includes the rows with the 'Point of Contact' column set to 'john.doe@gmail.com'

{
"rows":[
{
"name":"tables/TABLE_ID/rows/a6tvEPska7l8rAlHlSdOLb",
"values":{
"Thing to do":"Second item",// Text
"Size":110,// Number
"ETA":{"month":12,"day":3,"year":2021}// Date
"Stage":"Completed",// Dropdown
"Checklist":[// Checklist
"Do this",
"then this",
"finally this"
],
"Labels":[// Tags
"Green",
"Orange"
],
"Address":{// Location
"latitude":45.740726470947266,
"longitude":-88.00206756591797,
"address":"6027 Holmes Lane, Sattler, TX 78130, USA"
},
"Archive?":false,// Checkbox
"ID#":2,// Auto ID
"Point of Contact":"john.doe@gmail.com",// Person
"Last updated":"October 9, 2020 6:35:38 PM EDT",
"Created on":"March 10, 2020 1:07:54 PM EST",
}
},
...// More rows
],
}

Create a row in a table

The following sample shows how to add a row to a table.

vartableID="TABLE_ID";//IDforthetable
vartableName="tables/"+tableID;
varvalues={
"Number Column":100,
"Text Column 2":"hello world",
"Date Column 3":newDate(),
"Dropdown Col.":"Dropdown value",
};
Area120Tables.Tables.Rows.create({values:values},tableName);

When you specify the values to set for the new row, the keys of the object key-value pairs must exactly match the case-sensitive titles of the table columns unless the writable column's type is a lookup or summary column. You set values for lookup and summary columns using the value for the relationship. You must update the value for the relationship using the relationship name found in the Relationships dialog.

The acceptable values for a column depend on the column’s data type:

The Tables service makes a best-effort attempt to convert given values to match the column type. If the data doesn’t match, it won’t set the value and leaves it as blank for new rows.

Add multiple rows to a table

The following sample shows how to add multiple rows to a table at the same time.

vartableID="TABLE_ID";
vartableName="tables/"+tableID;
Area120Tables.Tables.Rows.batchCreate({requests:[
{row:{values:{"Col 1":"Sample","Col 2":"One","Col 3":"A"}}},
{row:{values:{"Col 1":"Example","Col 2":"Two","Col 3":"B"}}},
{row:{values:{"Col 1":"Test","Col 2":"Three","Col 3":"C"}}},
]},tableName)

Update a row in a table

The following sample shows how to update the values of an existing row in a table:

varrowName="tables/TABLE_ID/rows/ROW_ID";
varvalues={"Column":"HELLO"};
varresponse=Area120Tables.Tables.Rows.patch({values:values},rowName);
Logger.log("Update row:"+JSON.stringify(response));

Find the row ID

You can find the ID for a row two ways:

Get the row ID with the API

When you read rows from a table, you can use the name attribute for each row, which includes the table and row IDs.

Get the row ID from the Tables UI

1. Open the table in the Tables web app.
2. Right-click the row.
3. Click Get link to this row.
4. Paste the URL somewhere so that you can copy the ID.
5. Within the URL, the ID is after /row/.

The below sample shows where to find the row ID in the URL:

https://tables.area120.google.com/table/TABLE_ID/row/ROW_ID

Update multiple rows in a table

The following sample shows how to update the values of multiple rows in a table:

vartableID="TABLE_ID";
vartableName="tables/"+tableID;
varrequests=[
{row:{name:"tables/TABLE_ID/rows/ROW_ID_1",values:{"Column":"WORLD"}}},
{row:{name:"tables/TABLE_ID/rows/ROW_ID_2",values:{"Column":"WORLD"}}},
{row:{name:"tables/TABLE_ID/rows/ROW_ID_3",values:{"Column":"WORLD"}}},
];
varresponse=Area120Tables.Tables.Rows.batchUpdate({requests:requests},tableName);
Logger.log("Batch update rows:"+JSON.stringify(response));

Delete a row in a table

The following sample shows how to delete a single row from a table:

varrowName="tables/TABLE_ID/rows/ROW_ID";
varresponse=Area120Tables.Tables.Rows.remove(rowName);
Logger.log("Delete row:"+JSON.stringify(response));

Delete multiple rows in a table

The following sample shows how to delete multiple rows in a table:

vartableID="TABLE_ID";
vartableName="tables/"+tableID;
varrowNames=[
"tables/TABLE_ID/rows/ROW_ID_1",
"tables/TABLE_ID/rows/ROW_ID_2",
"tables/TABLE_ID/rows/ROW_ID_3",
];
Area120Tables.Tables.Rows.batchDelete({names:rowNames},tableName);

Restore deleted rows

You can restore deleted rows from the Tables UI. To restore a deleted row, follow the steps below:

1. On your computer, open the Tables web app.
2. Open the table you want to restore rows in.
3. At the top, click Show deleted rows and columns history.
4. Click Deleted rows.
5. At the right of the row you want to restore, click Restore from trash restore_from_trash.

Get a list of workspaces

The following sample shows how to get a list of all the workspaces the user owns.

//Getlistofworkspacestheuserownsandliststhetablesineachone:
varresponse=Area120Tables.Workspaces.list();
if(response){
varworkspaces=response.workspaces;
for(varworkspaceofworkspaces){
Logger.log(workspace.displayName);
for(vartableofworkspace.tables){
Logger.log('Table: '+table);
}
}
}

Below is an example of the output logs:

My Workspace
Table: Table 1
Table: Table 2
My TODOs
Table: Tasks