Workaround: Using Google Sheets Tables with Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

Abstract

Google Sheets’ new Tables feature enhances data organization but lacks direct management via Apps Script. This report proposes a workaround solution using Apps Script until native support arrives.

Introduction

Google Sheets recently introduced a new feature called Tables. Ref Tables offer a powerful way to organize and manage your data by transforming unformatted ranges into structured datasets with automatic headers, filtering options, and data validation capabilities. This not only improves the readability and maintainability of your spreadsheets but also allows for seamless integration with existing Google Sheets functions.

However, there’s a current limitation: while Tables offer significant benefits, Google Apps Script currently lacks built-in methods for directly managing them. This report addresses this gap by proposing a workaround solution utilizing Google Apps Script. It’s important to note that this workaround might be temporary, as future updates to Google Apps Script might introduce native support for managing Tables.

Current limitations

These limitations might be resolved in a future update.

Google Apps Script and Google Sheets Tables

  • Direct Management: Currently, there are no built-in methods in Google Apps Script to directly create, and modify the metadata of Google Sheets Tables. However, you can access and manipulate the data within existing Tables using Google Apps Script.
  • Table Creation: Google Apps Script cannot directly create new Tables. Tables must be manually created within Google Sheets.

Google Sheets Functions and Tables

  • Direct Access: When using Google Sheets functions, you can directly reference Tables by their names.
  • Table Name Retrieval: It’s important to note that there’s no direct way to retrieve the names of all Tables in a spreadsheet using Google Apps Script. You’ll need to manually specify the Table names when accessing them within your scripts.

Current workaround

In this workaround, we attempt to access the table data by combining Google Sheets functions and Google Apps Script. This approach allows us to leverage the power of both tools to dynamically retrieve and manipulate data from the table.

Sample tables on a Google Spreadsheet

The sample tables are as follows. It supposes that these tables are put into “Sheet1”.

Retrieve values using Table name

The sample formula for retrieving the values of SampleTable1 shown in the above image using the Table name is as follows.

=LET(
  tableName, SampleTable1,
  ARRAYFORMULA(OFFSET(tableName, -1,0,ROWS(tableName)+1))
)

When =ARRAYFORMULA(SampleTable1) is used, the header row is not included. This formula will be useful only when you want the data rows. But, when you want whole table including the header row, the above formula might be useful.

When the values of SampleTable1 are retrieved using Google Apps Script with the Table name, the sample script is as follows.

function sample1() {
  const tableName = "SampleTable1";

  const formula = `=LET(
  tableName, ${tableName},
  ARRAYFORMULA(OFFSET(tableName, -1,0,ROWS(tableName)+1))
)`;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tempSheet = ss.insertSheet(`smaple_${Utilities.getUuid()}`);
  tempSheet.getRange("A1").setFormula(formula);
  SpreadsheetApp.flush();
  const values = tempSheet.getDataRange().getValues(); // Values of the table.
  ss.deleteSheet(tempSheet);
  console.log(values);
}

When this script is run to the above tables in the sample image, the following result is obtained.

[
  ["B2", "C2", "D2", "E2", "F2"],
  ["B3", "C3", "D3", "E3", "F3"],
  ["B4", "C4", "D4", "E4", "F4"],
  ["B5", "C5", "D5", "E5", "F5"],
  ["B6", "C6", "D6", "E6", "F6"]
]

Retrieve A1Notation of Table using Table name

The sample formula for retrieving the A1Notation of SampleTable2 shown in the above image using the Table name is as follows. (I believe that this sample formula will be able to be simpler.)

=LET(
  tableName, SampleTable2,
  regexText, "'?([^']+)'?!",
  tableAdress, CELL("address", tableName),
  checkAddress, REGEXMATCH(CELL("address",tableName),regexText),
  sheetName, IF(checkAddress,REGEXEXTRACT(tableAdress,"'?([^']+)'?!"),""),
  rowPos, ROW(tableName) - 1,
  colPos, COLUMN(tableName),
  rowLength, ROWS(tableName) + 1,
  colLength, COLUMNS(tableName),
  a1Notation1, ADDRESS(rowPos,colPos,4),
  a1Notation2, ADDRESS(rowPos + rowLength - 1,colPos + colLength - 1,4),
  IF(sheetName<>"", "'"&sheetName&"'!"&a1Notation1&":"&a1Notation2, a1Notation1&":"&a1Notation2)
)

When the above tables are put into “Sheet1”, this formula is run at “Sheet2”, the following result is obtained.

'Sheet1'!B9:H13

In this case, when the formula is run on the same sheet as the tables, the sheet name is not included because of the specification of “CELL”.

Also, when this A1Notation is used, the values of the table can be retrieved using Google Apps Script. The sample script is as follows.

function sample2() {
  const tableName = "SampleTable2";

  const formula = `=LET(
  tableName, ${tableName},
  regexText, "'?([^']+)'?!",
  tableAdress, CELL("address", tableName),
  checkAddress, REGEXMATCH(CELL("address",tableName),regexText),
  sheetName, IF(checkAddress,REGEXEXTRACT(tableAdress,"'?([^']+)'?!"),""),
  rowPos, ROW(tableName) - 1,
  colPos, COLUMN(tableName),
  rowLength, ROWS(tableName) + 1,
  colLength, COLUMNS(tableName),
  a1Notation1, ADDRESS(rowPos,colPos,4),
  a1Notation2, ADDRESS(rowPos + rowLength - 1,colPos + colLength - 1,4),
  IF(sheetName<>"", "'"&sheetName&"'!"&a1Notation1&":"&a1Notation2, a1Notation1&":"&a1Notation2)
)`;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tempSheet = ss.insertSheet(`smaple_${Utilities.getUuid()}`);
  const range = tempSheet.getRange("A1");
  range.setFormula(formula);
  SpreadsheetApp.flush();
  const a1Notation = range.getValue(); // A1Notation of the table.
  ss.deleteSheet(tempSheet);
  const values = ss.getRange(a1Notation).getValues(); // Values of the table.
  console.log(values);
}

When this script is run to the above tables in the sample image, the following result is obtained.

[
  ["B9", "C9", "D9", "E9", "F9", "G9", "H9"],
  ["B10", "C10", "D10", "E10", "F10", "G10", "H10"],
  ["B11", "C11", "D11", "E11", "F11", "G11", "H11"],
  ["B12", "C12", "D12", "E12", "F12", "G12", "H12"],
  ["B13", "C13", "D13", "E13", "F13", "G13", "H13"]
]

Also, when the A1Notation of the table can be known, the values on the table can also be updated using Google Apps Script. This might be an important point.

Note

  • There might be cases where specific table values, such as dropdown list selections (DataValidation), cannot be retrieved directly. In these scenarios, a workaround is needed. You can find a workaround in my Stack Overflow answer.

Summary

This report explored methods for retrieving data values and A1Notation from tables in Google Spreadsheets from the table name using Google Apps Script with the current workaround. Currently, Google Apps Script lacks functionalities for directly manipulating tables within Spreadsheets. However, future updates are expected to address this limitation. For instance, the ability to retrieve the list of tables in a spreadsheet would significantly enhance table management using Google Apps Script.

 Share!