Skip to main content
Go to the Studio
Google

Google Sheets

Google Sheets

The Google Sheets module is responsible for actions that integrate the Google Sheets tool. Its methods can be accessed as in the example below:

js
const sheetsConnection = await API.Google.Sheets.Connection["v1_0_0"]({
  connectionId: "id-da-conexao-configurada-no-workspace",
});

Methods​

Connection​

Method responsible for establishing a connection with Google Sheets from a connection configured in the workspace.

js
const sheetsConnection = await API.Google.Sheets.Connection["v1_0_0"]({
  connectionId: "id-da-conexao-configurada-no-workspace",
});

Required parameters​

  • connectionId:String - expects the ID of the Google connection configured in the workspace (Workspace → Connections).

Optional parameters​

This method has no optional parameters.

Return​

It returns the authenticated Google Sheets connection instance (an instance of the sheets_v4.Sheets client of the googleapis SDK), which must be stored in a variable (e.g. sheetsConnection) and reused as the sheetsConnection parameter in the other methods of this module.

Spreadsheets.Create​

Method responsible for creating a new Google Sheets spreadsheet.

js
const createSpreadsheetResult = await API.Google.Sheets.Spreadsheets.Create["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  title: "Minha planilha",
  sheets: [
    { properties: { title: "Aba2" } },
  ],
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • title:String - expects the title the new spreadsheet will have.

Optional parameters​

  • sheets:Array - expects a list of tab objects (in the Schema$Sheet format of the Google API) to be created besides the default tab. Each item accepts at least properties.title:String, and optionally properties.hidden:Boolean, properties.index:Number and properties.gridProperties:Object (with rowCount:Number, columnCount:Number and frozenRowCount:Number). When it is not provided, the spreadsheet is created with the default tab only.

Return​

js
actions.createSpreadsheetResult.spreadsheetId // ID da planilha recém criada (String)
actions.createSpreadsheetResult.spreadsheetUrl // URL da planilha recém criada (String)
actions.createSpreadsheetResult.title // Título da planilha recém criada (String)

Spreadsheets.Get​

Method responsible for getting the metadata of a spreadsheet through its ID.

js
const spreadsheetResult = await API.Google.Sheets.Spreadsheets.Get["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet to be obtained.

Optional parameters​

This method has no optional parameters.

Return​

js
actions.spreadsheetResult.spreadsheetId // ID da planilha obtida (String)
actions.spreadsheetResult.title // Título da planilha obtida (String)
actions.spreadsheetResult.spreadsheetUrl // URL da planilha obtida (String)
actions.spreadsheetResult.sheets // Lista com o sheetId, title e index de cada aba da planilha (Array)

Sheets.Create​

Method responsible for adding a new tab to an existing spreadsheet.

js
const createSheetResult = await API.Google.Sheets.Sheets.Create["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  title: "Aba1",
  index: 0,
  rowCount: 1000,
  columnCount: 26,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet where the new tab will be created.
  • title:String - expects the title of the new tab.

Optional parameters​

  • index:Number - expects the position (index) where the new tab must be inserted among the other tabs of the spreadsheet.
  • rowCount:Number - expects the number of rows of the new tab.
  • columnCount:Number - expects the number of columns of the new tab.

Return​

js
actions.createSheetResult.sheetId // ID da aba recém criada (Number)
actions.createSheetResult.title // Título da aba recém criada (String)
actions.createSheetResult.index // Posição (index) da aba recém criada (Number)

Sheets.Get​

Method responsible for getting the metadata of a tab through its ID or title.

js
const sheetResult = await API.Google.Sheets.Sheets.Get["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  title: "Aba1",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet where the desired tab is.

Optional parameters​

  • sheetId:Number - expects the numeric ID of the tab to be obtained.
  • title:String - expects the title of the tab to be obtained.

It is mandatory to provide at least one of the two fields above (sheetId or title) to locate the tab; if both are provided, sheetId takes priority in the search.

Return​

js
actions.sheetResult.sheetId // ID da aba obtida (Number)
actions.sheetResult.title // Título da aba obtida (String)
actions.sheetResult.index // Posição (index) da aba obtida (Number)
actions.sheetResult.rowCount // Quantidade de linhas da aba obtida (Number)
actions.sheetResult.columnCount // Quantidade de colunas da aba obtida (Number)

Sheets.Delete​

Method responsible for deleting a tab of a spreadsheet.

js
await API.Google.Sheets.Sheets.Delete["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet that contains the tab to be deleted.
  • sheet:String|Number - expects the name or the numeric ID of the tab to be deleted.

Optional parameters​

This method has no optional parameters.

Return​

This method has no return.

Sheets.DeleteRowsOrColumns​

Method responsible for deleting a range of rows or columns of a tab.

js
await API.Google.Sheets.Sheets.DeleteRowsOrColumns["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  toDelete: "ROWS",
  startIndex: 2,
  amount: 3,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet that contains the tab.
  • sheet:String|Number - expects the name or the numeric ID of the tab.
  • toDelete:String - expects "ROWS" or "COLUMNS", indicating what will be deleted.
  • startIndex:Number - expects the initial position of the row or column from which the deletion will start, counting 1 as the first row (or column) of the tab.

Optional parameters​

  • amount:Number - expects how many rows or columns, starting from the initial position, must be deleted. When it is not provided, the default is 1.

Return​

This method has no return.

Values.Get​

Method responsible for getting the values of a range of cells.

js
const valuesResult = await API.Google.Sheets.Values.Get["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  range: "Página1!A1:D10",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • range:String - expects the A1 notation of the range to be read (e.g. Página1!A1:D10). To get every value of a tab, provide only its name (e.g. Página1).

Optional parameters​

This method has no optional parameters.

Return​

js
actions.valuesResult.range // notação A1 do intervalo que foi lido (String)
actions.valuesResult.values // valores do intervalo de células, como uma lista de linhas (Array)

Values.Update​

Method responsible for updating the values of a range of cells.

js
const updateValuesResult = await API.Google.Sheets.Values.Update["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  range: "Página1!A1:B2",
  values: [
    ["Nome", "Email"],
    ["João", "joao@email.com"],
  ],
  valueInputOption: "USER_ENTERED",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • range:String - expects the A1 notation of the range to be updated (e.g. Página1!A1:D10).
  • values:Array - expects a list of rows, where each row is a list of column values, to be written into the range provided.

Optional parameters​

  • valueInputOption:String - expects "USER_ENTERED" (formulas, dates etc. are interpreted) or "RAW" (no interpretation). When it is not provided, the default is "USER_ENTERED".

Return​

js
actions.updateValuesResult.updatedRange // notação A1 do intervalo que foi atualizado (String)
actions.updateValuesResult.updatedRows // quantidade de linhas atualizadas (Number)
actions.updateValuesResult.updatedColumns // quantidade de colunas atualizadas (Number)
actions.updateValuesResult.updatedCells // quantidade de células atualizadas (Number)

Values.Clear​

Method responsible for clearing the values of a range of cells.

js
const clearValuesResult = await API.Google.Sheets.Values.Clear["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  range: "Página1!A1:D10",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • range:String - expects the A1 notation of the range to be cleared (e.g. Página1!A1:D10).

Optional parameters​

This method has no optional parameters.

Return​

js
actions.clearValuesResult.clearedRange // notação A1 do intervalo que foi limpo (String)

Rows.Append​

Method responsible for adding a new row at the end of a tab, relating the values to the column headers.

js
const appendResult = await API.Google.Sheets.Rows.Append["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  values: {
    "Nome": "João",
    "Email": "joao@email.com",
  },
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.
  • values:Object - expects an object relating the name of each column (according to the tab header) to the desired value. Header columns not provided are left blank in the new row.

Optional parameters​

This method has no optional parameters.

Return​

js
actions.appendResult.rowNumber // número da linha adicionada (Number)
actions.appendResult.row // a linha adicionada, como objeto (Object)

Rows.Find​

Method responsible for finding the rows where a column matches a value provided.

js
const findResult = await API.Google.Sheets.Rows.Find["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  column: "Email",
  value: "joao@email.com",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.
  • column:String - expects the exact name of the column, as it appears in the tab header.
  • value:Any - expects the value to be compared with the column content, for each row of the tab.

Optional parameters​

This method has no optional parameters.

Return​

Important

Unlike the no-code version (which shows only the first row found), the return via code brings every row matching the value searched for, inside a rows list. The first occurrence (the lowest row number) is in rows[0].

js
actions.findResult.rows // lista de linhas encontradas, cada uma com rowNumber e row (Array)
actions.findResult.rows[0].rowNumber // número da primeira linha encontrada (Number)
actions.findResult.rows[0].row // a primeira linha encontrada, como objeto (Object)

Rows.Update​

Method responsible for finding the first row where a column matches a value provided (or a row by its number) and updating it.

js
const updateRowResult = await API.Google.Sheets.Rows.Update["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  by: "FILTER",
  column: "Email",
  value: "joao@email.com",
  values: {
    "Status": "Concluído",
  },
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.
  • by:String - expects "FILTER" (locate by column and value) or "ROW_NUMBER" (locate by row number).
    • When by is "FILTER": the column:String and value:Any fields become required, to locate the first row where the column provided matches the value.
    • When by is "ROW_NUMBER": the rowNumber:Number field becomes required (minimum value 2, since row 1 is the header).
  • values:Object - expects an object relating the name of each column to the new value to be written into the row located. Columns not provided keep their current value.

Optional parameters​

This method has no optional parameters.

Return​

js
actions.updateRowResult.rowNumber // número da linha atualizada (Number)
actions.updateRowResult.row // a linha após a atualização, como objeto (Object)

Tables.Create​

Method responsible for creating a table (named range) in a tab, from a header row.

js
const createTableResult = await API.Google.Sheets.Tables.Create["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  name: "Clientes",
  headers: ["Nome", "Email"],
  startRow: 1,
  startColumn: 1,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet where the table will be created.
  • sheet:String|Number - expects the name or the numeric ID of the tab.
  • name:String - expects the name the table will have, used to reference it in the next actions.

Optional parameters​

  • headers:Array - expects a list of strings with the column headers. When it is not provided, the robot uses whatever is already written in the first row of the range as the table header.
  • startRow:Number - expects the number of the row, in the tab, where the table header starts. When it is not provided, the default is 1.
  • startColumn:Number - expects the number of the column, in the tab, where the table starts. When it is not provided, the default is 1.

Return​

js
actions.createTableResult.tableId // ID da tabela recém criada (String)
actions.createTableResult.name // Nome da tabela recém criada (String)
actions.createTableResult.startRow // Primeira linha da tabela (Number)
actions.createTableResult.endRow // Última linha da tabela (Number)
actions.createTableResult.startColumn // Primeira coluna da tabela (Number)
actions.createTableResult.endColumn // Última coluna da tabela (Number)
actions.createTableResult.headers // Lista com os cabeçalhos das colunas da tabela (Array)

Tables.List​

Method responsible for listing the tables (named ranges) in a spreadsheet.

js
const tablesResult = await API.Google.Sheets.Tables.List["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet whose tables will be listed.

Optional parameters​

This method has no optional parameters.

Return​

js
actions.tablesResult.tables // lista de tabelas, cada uma com tableId, name, sheet, startRow, endRow, startColumn e endColumn (Array)

The endRow and endColumn fields of each item may not be returned when the table has no defined row or column limit.

Tables.Rows.Append​

Method responsible for adding a new row at the end of a table, relating the values to the column headers.

js
const appendTableRowResult = await API.Google.Sheets.Tables.Rows.Append["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  tableName: "Clientes",
  values: {
    "Nome": "João",
    "Email": "joao@email.com",
  },
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • tableName:String - expects the name of the table (named range).
  • values:Object - expects an object relating the name of each column (according to the table header) to the desired value. Header columns not provided are left blank in the new row.

Optional parameters​

This method has no optional parameters.

Return​

js
actions.appendTableRowResult.rowNumber // número da linha recém adicionada (Number)
actions.appendTableRowResult.row // a linha adicionada, como objeto (Object)

Tables.Rows.Get​

Method responsible for getting a row of the table through its number.

js
const tableRowResult = await API.Google.Sheets.Tables.Rows.Get["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  tableName: "Clientes",
  rowNumber: 2,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • tableName:String - expects the name of the table (named range).
  • rowNumber:Number - expects the number of the row to be obtained, counting the table header row as row 1 (e.g. the first data row is row 2).

Optional parameters​

This method has no optional parameters.

Return​

js
actions.tableRowResult.rowNumber // número da linha obtida (Number)
actions.tableRowResult.row // a linha obtida, como objeto (Object)

Tables.Rows.Update​

Method responsible for updating a row of the table, located by the row number or by a column/value filter.

js
const updateTableRowResult = await API.Google.Sheets.Tables.Rows.Update["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  tableName: "Clientes",
  by: "FILTER",
  column: "Email",
  value: "joao@email.com",
  values: {
    "Status": "Concluído",
  },
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • tableName:String - expects the name of the table (named range).
  • by:String - expects "FILTER" (locate by column and value) or "ROW_NUMBER" (locate by row number).
    • When by is "FILTER": the column:String and value:Any fields become required.
    • When by is "ROW_NUMBER": the rowNumber:Number field becomes required (minimum value 1, counting the table header row as 1).
  • values:Object - expects an object relating the name of each column to the new value to be written into the row located. Columns not provided keep their current value.

Optional parameters​

This method has no optional parameters.

Return​

js
actions.updateTableRowResult.rowNumber // número da linha atualizada (Number)
actions.updateTableRowResult.row // a linha após a atualização, como objeto (Object)

Tables.Rows.Delete​

Method responsible for deleting a row of the table, located by the row number or by a column/value filter.

js
const deleteTableRowResult = await API.Google.Sheets.Tables.Rows.Delete["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  tableName: "Clientes",
  by: "FILTER",
  column: "Email",
  value: "joao@email.com",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • tableName:String - expects the name of the table (named range).
  • by:String - expects "FILTER" (locate by column and value) or "ROW_NUMBER" (locate by row number).
    • When by is "FILTER": the column:String and value:Any fields become required.
    • When by is "ROW_NUMBER": the rowNumber:Number field becomes required (minimum value 1, counting the table header row as 1).

Optional parameters​

This method has no optional parameters.

Return​

js
actions.deleteTableRowResult.rowNumber // número da linha excluída (Number)

Tables.Columns.Add​

Method responsible for adding a new column to a table.

js
const addColumnResult = await API.Google.Sheets.Tables.Columns.Add["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  tableName: "Clientes",
  name: "Telefone",
  index: 3,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet where the table is located.
  • tableName:String - expects the name of the table the new column will be added to.
  • name:String - expects the name the new column will have.

Optional parameters​

  • index:Number - expects the position where the new column must be inserted into the table (base 1). When it is not provided, or when it is greater than the current number of columns plus one, the column is added at the end of the table.

Return​

js
actions.addColumnResult.name // Nome da coluna recém adicionada (String)
actions.addColumnResult.index // Posição da coluna recém adicionada (Number)

Tables.Columns.Delete​

Method responsible for deleting a column of a table.

js
await API.Google.Sheets.Tables.Columns.Delete["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  tableName: "Clientes",
  name: "Telefone",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet where the table is located.
  • tableName:String - expects the name of the table the column will be deleted from.
  • name:String - expects the exact name of the column, as it appears in the table header.

Optional parameters​

This method has no optional parameters.

Return​

This method has no return.

Tables.Columns.Rename​

Method responsible for renaming a column of a table.

js
const renameColumnResult = await API.Google.Sheets.Tables.Columns.Rename["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  tableName: "Clientes",
  name: "Telefone",
  newName: "Celular",
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet where the table is located.
  • tableName:String - expects the name of the table that contains the column to be renamed.
  • name:String - expects the exact name of the current column, as it appears in the table header.
  • newName:String - expects the new name the column will have.

Optional parameters​

This method has no optional parameters.

Return​

js
actions.renameColumnResult.name // O novo nome da coluna (String)

StyleCells​

Method responsible for setting the background/text color, bold, italic, alignment and border of a range of cells.

js
await API.Google.Sheets.StyleCells["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  startRow: 1,
  endRow: 1,
  startColumn: 1,
  endColumn: 4,
  backgroundColor: { red: 0.9, green: 0.9, blue: 0.9 },
  textColor: { red: 0, green: 0, blue: 0 },
  bold: true,
  italic: false,
  fontSize: 12,
  horizontalAlignment: "CENTER",
  verticalAlignment: "MIDDLE",
  border: {
    style: "SOLID",
    color: { red: 0, green: 0, blue: 0 },
  },
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.

At least one of the style options below must be provided; otherwise, the robot interrupts the execution with an error.

Optional parameters​

  • startRow:Number - first row of the range to be styled.
  • endRow:Number - last row of the range to be styled (included in the range).
  • startColumn:Number - first column of the range to be styled (1 corresponds to column A).
  • endColumn:Number - last column of the range to be styled (included in the range).
  • backgroundColor:Object - expects an object with red, green and blue (Number, from 0 to 1) for the background color.
  • textColor:Object - expects an object with red, green and blue (Number, from 0 to 1) for the text color.
  • bold:Boolean - applies or removes bold from the text of the range.
  • italic:Boolean - applies or removes italic from the text of the range.
  • fontSize:Number - font size to be applied to the text of the range.
  • horizontalAlignment:String - expects "LEFT", "CENTER" or "RIGHT".
  • verticalAlignment:String - expects "TOP", "MIDDLE" or "BOTTOM".
  • border:Object - expects an object to apply a border around the range, with style:String ("SOLID", "SOLID_MEDIUM", "SOLID_THICK", "DASHED", "DOTTED" or "DOUBLE", default "SOLID") and an optional color:Object (same format as backgroundColor/textColor).

When startRow, endRow, startColumn and endColumn are not provided, the style is applied to the whole tab.

Return​

This method has no return.

CreateValidationRule​

Method responsible for creating a data validation rule (drop-down list or check box) in a range of cells.

js
await API.Google.Sheets.CreateValidationRule["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  startRow: 2,
  endRow: 100,
  startColumn: 3,
  endColumn: 3,
  type: "DROPDOWN",
  options: ["Ativo", "Inativo"],
  showDropdownArrow: true,
  strict: true,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.
  • type:String - expects "DROPDOWN" (drop-down list) or "CHECKBOX" (check box).
    • When type is "DROPDOWN": the options:Array field (list of strings) becomes required, with at least one option.

Optional parameters​

  • startRow:Number - first row of the range to be validated.
  • endRow:Number - last row of the range to be validated (included in the range).
  • startColumn:Number - first column of the range to be validated (1 corresponds to column A).
  • endColumn:Number - last column of the range to be validated (included in the range).
  • strict:Boolean - when it is true, Google Sheets rejects values that do not meet the validation rule; when it is false, it only shows a warning but allows the value to be saved. When it is not provided, the default is true.
  • showDropdownArrow:Boolean - applicable only when type is "DROPDOWN". It defines whether the selection arrow is shown in the cells of the range. When it is not provided, the default is true.

When startRow, endRow, startColumn and endColumn are not provided, the validation rule is applied to the whole tab.

Return​

This method has no return.

ProtectRange​

Method responsible for protecting a range of cells against editing.

js
const protectRangeResult = await API.Google.Sheets.ProtectRange["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  startRow: 1,
  endRow: 1,
  description: "Cabeçalho protegido",
  warningOnly: false,
  editorEmails: ["usuario@empresa.com"],
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.

Optional parameters​

  • startRow:Number - first row of the range to be protected.
  • endRow:Number - last row of the range to be protected (included in the range).
  • startColumn:Number - first column of the range to be protected (1 corresponds to column A).
  • endColumn:Number - last column of the range to be protected (included in the range).
  • description:String - description to identify the protection created.
  • warningOnly:Boolean - when it is true, the editing of the range is not actually blocked; only a warning is shown to whoever tries to edit it. When it is not provided, the default is false (it blocks the editing).
  • editorEmails:Array - list of e-mails (String, valid e-mail format) that will have permission to edit the range even with the protection active.

When startRow, endRow, startColumn and endColumn are not provided, the protection is applied to the whole tab.

Return​

js
actions.protectRangeResult.protectedRangeId // ID da proteção de intervalo recém criada (Number)

ApplyFilter​

Method responsible for applying a basic filter to a range of cells.

js
await API.Google.Sheets.ApplyFilter["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  startRow: 1,
  startColumn: 1,
  endColumn: 4,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.

Optional parameters​

  • startRow:Number - initial row of the range (1 = first row). When it is not provided, the filter is applied from the first row of the tab.
  • endRow:Number - final row of the range. When it is not provided, the filter is applied up to the last row of the tab.
  • startColumn:Number - initial column of the range (1 = first column). When it is not provided, the filter is applied from the first column of the tab.
  • endColumn:Number - final column of the range. When it is not provided, the filter is applied up to the last column of the tab.

Return​

This method has no return.

FindAndReplace​

Method responsible for finding and replacing a text in a spreadsheet, tab or range of cells.

js
const findAndReplaceResult = await API.Google.Sheets.FindAndReplace["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  find: "Pendente",
  replacement: "Concluído",
  matchCase: false,
  matchEntireCell: true,
  searchByRegex: false,
  includeFormulas: false,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • find:String - expects the text to be located.
  • replacement:String - expects the text that will replace the occurrences found.

Optional parameters​

  • sheet:String|Number - expects the name or the numeric ID of the tab. When it is not provided, the search and replacement are done in the whole spreadsheet (every tab).
  • startRow:Number - initial row of the search range. It only takes effect when sheet is also provided.
  • endRow:Number - final row of the search range. It only takes effect when sheet is also provided.
  • startColumn:Number - initial column of the search range. It only takes effect when sheet is also provided.
  • endColumn:Number - final column of the search range. It only takes effect when sheet is also provided.
  • matchCase:Boolean - when it is true, the search is case-sensitive. Default: false.
  • matchEntireCell:Boolean - when it is true, the replacement only happens when the entire content of the cell equals the text searched for. Default: false.
  • searchByRegex:Boolean - when it is true, the find field is interpreted as a regular expression. Default: false.
  • includeFormulas:Boolean - when it is true, the search also considers the text of the cell formulas. Default: false.

Return​

js
actions.findAndReplaceResult.valuesChanged // quantidade de valores alterados (Number)
actions.findAndReplaceResult.formulasChanged // quantidade de fórmulas alteradas (Number)
actions.findAndReplaceResult.rowsChanged // quantidade de linhas alteradas (Number)
actions.findAndReplaceResult.sheetsChanged // quantidade de abas alteradas (Number)
actions.findAndReplaceResult.occurrencesChanged // quantidade de ocorrências alteradas (Number)

SetVisibility​

Method responsible for hiding or showing a tab, or a range of rows/columns.

js
await API.Google.Sheets.SetVisibility["v1_0_0"]({
  sheetsConnection: sheetsConnection,
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheet: "Página1",
  hidden: true,
  target: "ROWS",
  startIndex: 5,
  amount: 2,
});

Required parameters​

  • sheetsConnection:Object - expects the connection instance obtained by the Connection method.
  • spreadsheetId:String - expects the ID of the spreadsheet.
  • sheet:String|Number - expects the name or the numeric ID of the tab.
  • hidden:Boolean - expects true to hide the target selected, or false to show it (make it visible).
  • target:String - expects "SHEET" (the whole tab), "ROWS" (rows) or "COLUMNS" (columns).
    • When target is "ROWS" or "COLUMNS": the startIndex:Number field becomes required, provided in base 1 (1 = first row or column).

Optional parameters​

  • amount:Number - applicable only when target is "ROWS" or "COLUMNS". Expects how many rows or columns, starting from startIndex, will have their visibility changed. When it is not provided, the default is 1.

Return​

This method has no return.