Following the improvements made to tables in Google Sheets in March and April, we’re excited to introduce API support for tables. Now, users will be able to take basic actions to create and modify tables via the Sheets API.
Following enhancements to Google Sheets tables last year, Google recently highlighted an update for developers: the Sheets API now supports tables. This is great news for the Google Workspace developer community, as it allows for direct programmatic creation and modification of tables, moving beyond previous reliance on workarounds.
For a while, developers have found clever ways to interact with table-like structures, such as Kanshi Tanaike’s notable 2024 solution using a mix of Apps Script and Sheet formulas. While these methods were very clever, the new, direct API support offers a more robust and straightforward way for interacting with tables in Google Sheets. For Google Apps Script users, for now this will require using the Google Sheets Advanced Service to call these new table methods, as direct integration into the SpreadsheetApp service hasn’t been announced at this time.
Key API Capabilities for Tables:
The Sheets API now lets developers:
Add Tables: Create tables with defined names, ranges, and specific column properties (like ‘PERCENT‘ or ‘DROPDOWN‘ with validation).
Update Tables: Modify table size (add/remove rows/columns), and toggle table footers. The API also provides methods like InsertRangeRequest and DeleteRangeRequest for more granular control.
Append Values: Easily add new rows to the end of a table using AppendCellsRequest, which intelligently handles existing data and footers.
Delete Tables: Remove entire tables and their content (DeleteTableRequest) or just the formatting while retaining data (DeleteBandingRequest).
Utilise Column Types: Work with various column types including numeric, date, dropdown, smart chip, and checkbox.
Great Martin!
Following the example given in the developers we need to call the API, Google Sheets API, create an object variable, as in the following example
function addTable() {
const spreadsheetId = SpreadsheetApp.getActive().getId()
const request =
{“requests”:
{
“addTable”: {
“table”: {
“name”: “Project Tracker”,
“tableId”: “123”,
“range”: {
“sheetId”: 0, //Sheet id
“startColumnIndex”: 0, // Start Number of Column (0 = A)
“endColumnIndex”: 5, // End Number of Column (1 = A)
“startRowIndex”: 0, // Start Number of Row (0 = A)
“endRowIndex”: 5, // End Number of Row (1 = 1)
},
“columnProperties”: [
{
“columnIndex”: 0,
“columnName”: “Column 1”,
“columnType”: “DATE”
},
{
“columnIndex”: 1,
“columnName”: “Column 2”,
“columnType”: “DROPDOWN”,
“dataValidationRule”: {
“condition”: {
“type”: “ONE_OF_LIST”,
“values”: [
{
“userEnteredValue”: “Not Started”
},
{
“userEnteredValue”: “In Progress”
},
{
“userEnteredValue”: “Complete”
}
]
}
}
}
],
}
}
}
}
Sheets.Spreadsheets.batchUpdate(request, spreadsheetId);
}
A small edit is required to build the JSON resource
function addTable() {
const resource = {
“requests”: [{
“addTable”: {
“table”: {…
}
}
}]
}
Sheets.Spreadsheets.batchUpdate(resource, SpreadsheetApp.getActiveSpreadsheet().getId());
}
The script works, it was at the beginning😅:
function addTable() {
const spreadsheetId = SpreadsheetApp.getActive().getId() (similar SpreadsheetApp.getActiveSpreadsheet().getId())