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.
// Get list of tables the user owns var response = Area120Tables . Tables . list (); if ( response ) { var tables = 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:
// Paginate through a list of tables var pageSize = 1000 ; var pageToken ; var response = Area120Tables . Tables . list ({ page_size : pageSize }); while ( response ) { var tables = response . tables ; // get next page of tables 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.
var
tableID
=
" TABLE_ID
"
;
//
ID
for
the
table
var
tableName
=
"tables/"
+
tableID
;
var
response
=
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_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 the field values.
var
tableID
=
" TABLE_ID
"
;
//
ID
for
the
table
var
pageToken
;
var
pageSize
=
1000
;
var
tableName
=
"tables/"
+
tableID
;
var
response
=
Area120Tables
.
Tables
.
Rows
.
list
(
tableName
)
if
(
response
)
{
for
(
var
i
=
0
,
rows
=
response
.
rows
;
i
<
rows
.
length
;
i
++
)
{
if
(
!
rows
[
i
]
.
values
)
{
//
If
blank
row
,
keep
going
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:
var pageToken ; var pageSize = 1000 ; var response = Area120Tables . Tables . Rows . list ( tableName , { page_size : pageSize }); while ( response ) { var rows = response . rows ; // read next page of rows 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.
var tableID = "TABLE_ID" ; // ID for the table var tableName = "tables/" + tableID ; var rowID = "ROW_ID" ; // ID for the row to fetch var rowName = tableName + "/rows/" + rowID ; // Construct row name var response = 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
.
var
tableID
=
" TABLE_ID
"
;
//
ID
for
the
table
var
pageToken
;
var
pageSize
=
1000
;
var
tableName
=
"tables/"
+
tableID
;
var
response
=
Area120Tables
.
Tables
.
Rows
.
list
(
tableName
,
{
filter
:
"values.\"
Point
of
Contact
\
"=\"
john
.
doe
@gmail
.
com
\
""
}
)
if
(
response
)
{
for
(
var
i
=
0
,
rows
=
response
.
rows
;
i
<
rows
.
length
;
i
++
)
{
if
(
!
rows
[
i
]
.
values
)
{
//
If
blank
row
,
keep
going
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.
var
tableID
=
" TABLE_ID
"
;
//
ID
for
the
table
var
tableName
=
"tables/"
+
tableID
;
var
values
=
{
"Number Column"
:
100
,
"Text Column 2"
:
"hello world"
,
"Date Column 3"
:
new
Date
(),
"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:
String
String
Number
Number
Date
Object {
"year": Number,
"month": Number,
"day": Number
}
Date
, String
(in most date formats)String
(email address)String
(must match Google user)Object[] {
"id": String,
"name": String,
"mimeType": String,
"url": String
}
Object {
"latitude": Number,
"longitude": Number,
"address": String
}
Object {
"latitude": Number (required),
"longitude": Number (required),
"address": String
}
String
String
(must match the dropdown options)String[]
(array of tag options)String[]
(must match the tag options)Boolean
Boolean
String[]
(array of list items)String[]
(must match the list items)String
String: "tables/[LINKED_TABLE_ID]/rows/[LINKED_ROW_ID]"
Count :
Number
Max on a Date-type column :
String
List Values :
Array
Number
String
Object {
“seconds”: Number,
“nanos”: Number
}
String
Object {
“seconds”: Number,
“nanos”: Number
}
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.
var
tableID
=
“
TABLE_ID
”
;
var
tableName
=
"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:
var rowName = "tables/TABLE_ID/rows/ROW_ID" ; var values = { "Column" : "HELLO" }; var response = 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
- Open the table in the Tables web app .
- Right-click the row.
- Click Get link to this row.
- Paste the URL somewhere so that you can copy the ID.
- 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:
var tableID = “TABLE_ID” ; var tableName = "tables/" + tableID ; var requests = [ { 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" }}}, ]; var response = 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:
var rowName = "tables/TABLE_ID/rows/ROW_ID" ; var response = 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:
var tableID = “TABLE_ID” ; var tableName = "tables/" + tableID ; var rowNames = [ "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:
- On your computer, open the Tables web app .
- Open the table you want to restore rows in.
- At the top, click Show deleted rows and columns .
- Click Deleted rows.
- 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 user owns.
// Get list of workspaces the user owns and lists the tables in each one : var response = Area120Tables . Workspaces . list (); if ( response ) { var workspaces = response . workspaces ; for ( var workspace of workspaces ){ Logger . log ( workspace . displayName ); for ( var table of workspace . 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
