Google is committed to advancing racial equity for Black communities. See how.

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.

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 100 tables. 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;
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});
  }
}

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_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.

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": "March 4, 2020",       // 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 100 rows. To retrieve more rows, paginate the responses using the page_token and page_size parameters, shown below:

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.

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 titles of the table columns. The column names are case sensitive.

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

Column type Data type (read) Acceptable input types (write)
Standard data
Text String String
Number Number Number
Date Date
Object {
"year": Number,
"month": Number,
"day": Number
}
Date, String (in most date formats)
Rich data
Person String (email address) String (must match Google user)
File attachment Object[] {
"id": String,
"name": String,
"mimeType": String,
"url": String
}
This field can’t be modified with the API.
Location Object {
"latitude": Number,
"longitude": Number,
"address": String
}
Object {
"latitude": Number (required),
"longitude": Number (required),
"address": String
}
Rich entry
Dropdown String String (must match the dropdown options)
Tags String[] (array of tag options) String[] (must match the tag options)
Checkbox Boolean Boolean
Checklist String[] (array of list items) String[] (must match the list items)
Linked data
Lookup Depends on the source column type. This field can’t be modified.
Summary Depends 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 ID Number This field can't be modified.
Metadata
Creator String This field can't be modified.
Create time Object {
“seconds”: Number,
“nanos”: Number
}
This field can't be modified.
Updater String This field can't be modified.
Update time Object {
“seconds”: Number,
“nanos”: Number
}
This field can't be modified.

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));
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 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:

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 rows in a table

The following sample shows how to delete a 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));

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 .
  4. Click Deleted rows.
  5. At the right of the row you want to restore, click Restore from trash .