Tables service

  • The Tables service enables scripts to programmatically interact with Google Tables, allowing for reading and editing of rows.

  • This is an advanced service that requires explicit enabling before use.

  • The Tables service uses the same objects, methods, and parameters as the public Tables API.

  • The provided content includes sample code for common operations such as listing tables and rows, getting table and row information, and creating, updating, and deleting rows.

  • Batch operations for creating, updating, and deleting rows are supported, with a limit of up to 500 rows per batch.

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

Note: This is an advanced service that must beenabled before use.

Reference

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

To report issues and find other support, see theTables 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.

//Getlistoftablestheuserownsvarresponse=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 tableand 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 thepage_token andpage_size parameters, shownbelow:

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

The max value of thepage_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 columndefinition.

vartableID="TABLE_ID";//IDforthetablevartableName="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 theTables 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_IDhttps://tables.area120.google.com/u/0/table/TABLE_IDhttps://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 thefield values.

vartableID="TABLE_ID";//IDforthetablevarpageToken;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,keepgoingLogger.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 inthe 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, paginatethe responses using thepage_token andpage_size parameters, shown below:

varpageToken;varpageSize=1000;varresponse=Area120Tables.Tables.Rows.list(tableName,{page_size:pageSize});while(response){varrows=response.rows;//readnextpageofrowspageToken=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 anextPageToken.Otherwise, the response is undefined. To retrieve the next page of results, passin thenextPageToken to the next list call.

The max value of thepage_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";//IDforthetablevartableName="tables/"+tableID;varrowID="ROW_ID";//IDfortherowtofetchvarrowName=tableName+"/rows/"+rowID;//Constructrownamevarresponse=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 areinterested in, use thefilter parameter. For more details on the syntax andcolumn types supported by the filter, please take a look at thefiltering API documentation.

vartableID="TABLE_ID";//IDforthetablevarpageToken;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,keepgoingLogger.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";//IDforthetablevartableName="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 objectkey-value pairs must exactly match the case-sensitive titles of the tablecolumns unless the writable column's type is alookup orsummary column. Youset values forlookup andsummary columns using the value for therelationship. You must update the value for the relationship using therelationship name found in theRelationships dialog.

Note: When you create column titles, make sure the title doesn’t have trailingspaces to avoid confusion.

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

Column typeData type (read)Acceptable input types (write)
Standard data
TextStringString
NumberNumberNumber
DateDate
Object {
"year": Number,
"month": Number,
"day": Number
}		Date,String (in most date formats)
Rich data
PersonString (email address)String (must match Google user)
File attachmentObject[] {
"id": String,
"name": String,
"mimeType": String,
"url": String
}		This field can’t be modified with the API.
LocationObject {
"latitude": Number,
"longitude": Number,
"address": String
}		Object {
"latitude": Number (required),
"longitude": Number (required),
"address": String
}
Rich entry
DropdownStringString (must match the dropdown options)
TagsString[] (array of tag options)String[] (must match the tag options)
CheckboxBooleanBoolean
ChecklistString[] (array of list items)String[] (must match the list items)
Linked data
RelationshipStringString: "tables/[LINKED_TABLE_ID]/rows/[LINKED_ROW_ID]"
LookupDepends on the source column type.This field can’t be modified and will update with the linked value.
SummaryDepends on the source column type and summary function:
Count:Number
Max on a Date-type column:String
List Values:Array		This field can’t be modified.
Calculated field
Auto IDNumberThis field can't be modified.
Metadata
CreatorStringThis field can't be modified.
Create timeObject {
“seconds”: Number,
“nanos”: Number
}		This field can't be modified.
UpdaterStringThis field can't be modified.
Update timeObject {
“seconds”: Number,
“nanos”: Number
}		This field can't be modified.

The Tables service makes a best-effort attempt to convert given values to matchthe column type. If the data doesn’t match, it won’t set the value and leaves itas 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)

Note: Batch operations can include up to 500 rows.

Update a row in a table

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

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));
The response returns the updated row.

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 thename attribute for each row,which includes the table and row IDs.

Get the row ID from the Tables UI
  1. Open the table in theTables web app.
  2. Right-click the row.
  3. ClickGet 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));

Note: Batch operations can include up to 500 rows.

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 theTables web app.
  2. Open the table you want to restore rows in.
  3. At the top, click Show deleted rows and columns.
  4. ClickDeleted rows.
  5. At the right of the row you want to restore, click Restore from trash.

Get a list of workspaces

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

//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 WorkspaceTable: Table 1Table: Table 2My TODOsTable: Tasks

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-11 UTC.