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.

// 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_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":{"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:

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
Relationship String String: "tables/[LINKED_TABLE_ID]/rows/[LINKED_ROW_ID]"
Lookup Depends on the source column type. This field can’t be modified and will update with the linked value.
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 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:

  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 .

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