跳到主要内容

Google Sheets 文档内工作表操作#

使用此操作在 Google Sheets 的 Google 电子表格中创建、更新、清除或删除工作表。有关 Google Sheets 节点本身的更多信息，请参阅 Google Sheets。

此节点可用作 AI 工具

此节点可用于增强 AI 代理的功能。以这种方式使用时，许多参数可以自动设置，或使用 AI 指导的信息设置 - 在 AI 工具参数文档中了解更多信息。

追加或更新行#​

使用此操作更新现有行，或者如果在工作表中未找到匹配条目，则在数据末尾添加新行。

输入这些参数：

  • Credential to connect with（连接凭证）：创建或选择现有的 Google Sheets 凭证。
  • Resource（资源）：选择 Sheet Within Document。
  • Operation（操作）：选择 Append or Update Row。
  • Document（文档）：选择包含你要追加或更新行的工作表的电子表格。
    • 选择 From list（从列表）从下拉列表中选择电子表格标题，By URL（按 URL）输入电子表格的 URL，或 By ID（按 ID）输入 spreadsheetId。
    • 你可以在 Google Sheets URL 中找到 spreadsheetId：https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0。
  • Sheet（工作表）：选择你要追加或更新行的工作表。
    • 选择 From list（从列表）从下拉列表中选择工作表标题，By URL（按 URL）输入工作表的 URL，By ID（按 ID）输入 sheetId，或 By Name（按名称）输入工作表标题。
    • 你可以在 Google Sheets URL 中找到 sheetId：https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId。
  • Mapping Column Mode（映射列模式）：
    • Map Each Column Manually（手动映射每列）：为每列输入 Values to Send（要发送的值）。
    • Map Automatically（自动映射）：n8n 自动查找与 Google Sheets 中的列匹配的传入数据。在此模式下，请确保传入数据字段与 Google Sheets 中的列相同。（如果需要，请在此节点之前使用 Edit Fields 节点更改它们。）
    • Nothing（无）：不映射任何数据。

选项#​

  • Cell Format（单元格格式）：使用此选项选择如何格式化单元格中的数据。有关更多信息，请参阅 Google Sheets API | CellFormat。
    • Let Google Sheets format（让 Google Sheets 格式化）（默认）：n8n 根据 Google Sheets 的默认设置格式化单元格中的文本和数字。
    • Let n8n format（让 n8n 格式化）：工作表中的新单元格将具有与 n8n 提供的输入数据相同的数据类型。
  • Data Location on Sheet（工作表上的数据位置）：当你需要指定工作表上的数据范围时，请使用此选项。
    • Header Row（标题行）：指定包含列标题的行索引。
    • First Data Row（第一个数据行）：指定实际数据开始的行索引。
  • Handling extra fields in input（处理输入中的额外字段）：使用 Mapping Column Mode > Map Automatically（映射列模式 > 自动映射）时，使用此选项决定如何处理输入数据中与工作表中任何现有列不匹配的字段。
    • Insert in New Column(s)（插入新列）（默认）：为任何额外数据添加新列。
    • Ignore Them（忽略它们）：忽略与现有列不匹配的额外数据。
    • Error（错误）：抛出错误并停止执行。
  • Use Append（使用追加）：打开此选项以使用 Google API 追加端点添加新数据行。
    • 默认情况下，n8n 追加空行或列，然后添加新数据。这种方法可以确保数据对齐，但可能效率较低。使用追加端点可以通过最小化 API 调用次数和简化流程来提高性能。但是，如果现有工作表数据存在不一致性，例如行和列之间的间隙或中断，n8n 可能会在错误的位置添加新数据，导致对齐问题。
    • 当性能是优先考虑的因素且工作表中的数据结构一致且没有间隙时，请使用此选项。

有关更多信息，请参阅 Method: spreadsheets.values.update | Google Sheets API 文档。

Append Row#​

Use this operation to append a new row at the end of the data in a sheet.

Enter these parameters:

  • Credential to connect with: Create or select an existing Google Sheets credentials.
  • Resource: Select Sheet Within Document.
  • Operation: Select Append Row.
  • Document: Choose a spreadsheet with the sheet you want to append a row to.
    • Select From list to choose the spreadsheet title from the dropdown list, By URL to enter the url of the spreadsheet, or By ID to enter the spreadsheetId.
    • You can find the spreadsheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
  • Sheet: Choose a sheet you want to append a row to.
    • Select From list to choose the sheet title from the dropdown list, By URL to enter the url of the sheet, By ID to enter the sheetId, or By Name to enter the sheet title.
    • You can find the sheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId.
  • Mapping Column Mode:
    • Map Each Column Manually: Select the Column to Match On when finding the rows to update. Enter Values to Send for each column.
    • Map Automatically: n8n looks for incoming data that matches the columns in Google Sheets automatically. In this mode, make sure the incoming data fields are the same as the columns in Google Sheets. (Use an Edit Fields node before this node to change them if required.)
    • Nothing: Don't map any data.

Options#​

  • Cell Format: Use this option to choose how to format the data in cells. Refer to Google Sheets API | CellFormat for more information.
    • Let Google Sheets format (default): n8n formats text and numbers in the cells according to Google Sheets' default settings.
    • Let n8n format: New cells in your sheet will have the same data types as the input data provided by n8n.
  • Data Location on Sheet: Use this option when you need to specify the data range on your sheet.
    • Header Row: Specify the row index that contains the column headers.
    • First Data Row: Specify the row index where the actual data starts.
  • Handling extra fields in input: When using Mapping Column Mode > Map Automatically, use this option to decide how to handle fields in the input data that don't match any existing columns in the sheet.
    • Insert in New Column(s) (default): Adds new columns for any extra data.
    • Ignore Them: Ignores extra data that don't match the existing columns.
    • Error: Throws an error and stops execution.
  • Use Append: Turn on this option to use the Google API append endpoint for adding new data rows.
    • By default, n8n appends empty rows or columns and then adds the new data. This approach can ensure data alignment but may be less efficient. Using the append endpoint can lead to better performance by minimizing the number of API calls and simplifying the process. But if the existing sheet data has inconsistencies such as gaps or breaks between rows and columns, n8n may add the new data in the wrong place, leading to misalignment issues.
    • Use this option when performance is a priority and the data structure in the sheet is consistent without gaps.

Refer to the Method: spreadsheets.values.append | Google Sheets API documentation for more information.

Clear a sheet#​

Use this operation to clear all data from a sheet.

Enter these parameters:

  • Credential to connect with: Create or select an existing Google Sheets credentials.
  • Resource: Select Sheet Within Document.
  • Operation: Select Clear.
  • Document: Choose a spreadsheet with the sheet you want to clear data from.
    • Select From list to choose the spreadsheet title from the dropdown list, By URL to enter the url of the spreadsheet, or By ID to enter the spreadsheetId.
    • You can find the spreadsheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
  • Sheet: Choose a sheet you want to clear data from.
    • Select From list to choose the sheet title from the dropdown list, By URL to enter the url of the sheet, By ID to enter the sheetId, or By Name to enter the sheet title.
    • You can find the sheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId.
  • Clear: Select what data you want cleared from the sheet.
    • Whole Sheet: Clear the entire sheet's data. Turn on Keep First Row to keep the first row of the sheet.
    • Specific Rows: Clear data from specific rows. Also enter:
      • Start Row Number: Enter the first row number you want to clear.
      • Number of Rows to Delete: Enter the number of rows to clear. 1 clears data only the row in the Start Row Number.
    • Specific Columns: Clear data from specific columns. Also enter:
      • Start Column: Enter the first column you want to clear using the letter notation.
      • Number of Columns to Delete: Enter the number of columns to clear. 1 clears data only in the Start Column.
    • Specific Range: Enter the table range to clear data from, in A1 notation.

Refer to the Method: spreadsheets.values.clear | Google Sheets API documentation for more information.

Create a new sheet#​

Use this operation to create a new sheet.

Enter these parameters:

  • Credential to connect with: Create or select an existing Google Sheets credentials.
  • Resource: Select Sheet Within Document.
  • Operation: Select Create.
  • Document: Choose a spreadsheet in which you want to create a new sheet.
    • Select From list to choose the spreadsheet title from the dropdown list, By URL to enter the url of the spreadsheet, or By ID to enter the spreadsheetId.
    • You can find the spreadsheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
  • Title: Enter the title for your new sheet.

Options#​

  • Hidden: Turn on this option to keep the sheet hidden in the UI.
  • Right To Left: Turn on this option to use RTL sheet instead of an LTR sheet.
  • Sheet ID: Enter the ID of the sheet.
    • You can find the sheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId
  • Sheet Index: By default, the new sheet is the last sheet in the spreadsheet. To override this behavior, enter the index you want the new sheet to use. When you add a sheet at a given index, Google increments the indices for all following sheets. Refer to Sheets | SheetProperties documentation for more information.
  • Tab Color: Enter the color as hex code or use the color picker to set the color of the tab in the UI.

Refer to the Method: spreadsheets.batchUpdate | Google Sheets API documentation for more information.

Delete a sheet#​

Use this operation to permanently delete a sheet.

Enter these parameters:

  • Credential to connect with: Create or select an existing Google Sheets credentials.
  • Resource: Select Sheet Within Document.
  • Operation: Select Delete.
  • Document: Choose a spreadsheet that contains the sheet you want to delete.
    • Select From list to choose the spreadsheet title from the dropdown list, By URL to enter the url of the spreadsheet, or By ID to enter the spreadsheetId.
    • You can find the spreadsheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
  • Sheet: Choose the sheet you want to delete.
    • Select From list to choose the sheet title from the dropdown list, By URL to enter the url of the sheet, By ID to enter the sheetId, or By Name to enter the name of the sheet.
    • You can find the sheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId.

Refer to the Method: spreadsheets.batchUpdate | Google Sheets API documentation for more information.

Delete Rows or Columns#​

Use this operation to delete rows or columns in a sheet.

Enter these parameters:

  • Credential to connect with: Create or select an existing Google Sheets credentials.
  • Resource: Select Sheet Within Document.
  • Operation: Select Delete Rows or Columns.
  • Document: Choose a spreadsheet that contains the sheet you want to delete rows or columns from.
    • Select From list to choose the spreadsheet title from the dropdown list, By URL to enter the url of the spreadsheet, or By ID to enter the spreadsheetId.
    • You can find the spreadsheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
  • Sheet: Choose the sheet in which you want to delete rows or columns.
    • Select From list to choose the sheet title from the dropdown list, By URL to enter the url of the sheet, By ID to enter the sheetId, or By Name to enter the name of the sheet.
    • You can find the sheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId.
  • Start Row Number or Start Column: Enter the row number or column letter to start deleting.
  • Number of Rows to Delete or Number of Columns to delete: Enter the number of rows or columns to delete.

Refer to the Method: spreadsheets.batchUpdate | Google Sheets API documentation for more information.

Get Row(s)#​

Use this operation to read one or more rows from a sheet.

Enter these parameters:

  • Credential to connect with: Create or select an existing Google Sheets credentials.
  • Resource: Select Sheet Within Document.
  • Operation: Select Get Row(s).
  • Document: Choose a spreadsheet that contains the sheet you want to get rows from.
    • Select From list to choose the spreadsheet title from the dropdown list, By URL to enter the url of the spreadsheet, or By ID to enter the spreadsheetId.
    • You can find the spreadsheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
  • Sheet: Choose a sheet you want to read rows from.
    • Select From list to choose the sheet title from the dropdown list, By URL to enter the url of the sheet, By ID to enter the sheetId, or By Name to enter the name of the sheet.
    • You can find the sheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId.
  • Filters: By default, the node returns all rows in the sheet. Set filters to return a limited set of results:
    • Column: Select the column in your sheet to search against.
    • Value: Enter a cell value to search for. You can drag input data parameters here. If your filter matches multiple rows, n8n returns the first result. If you want all matching rows:
      1. Under Options, select Add Option > When Filter Has Multiple Matches.
      2. Change When Filter Has Multiple Matches to Return All Matches.

Options#​

  • Data Location on Sheet: Use this option to specify a data range. By default, n8n will detect the range automatically until the last row in the sheet.
  • Output Formatting: Use this option to choose how n8n formats the data returned by Google Sheets.
  • General Formatting:
    • Values (unformatted) (default): n8n removes currency signs and other special formatting. Data type remains as number.
    • Values (formatted): n8n displays the values as they appear in Google Sheets (for example, retaining commas or currency signs) by converting the data type from number to string.
    • Formulas: n8n returns the formula. It doesn't calculate the formula output. For example, if a cell B2 has the formula =A2, n8n returns B2's value as =A2 (in text). Refer to About date & time values | Google Sheets for more information.
  • Date Formatting: Refer to DateTimeRenderOption | Google Sheets for more information.
    • Formatted Text (default): As displayed in Google Sheets, which depends on the spreadsheet locale. For example 01/01/2024.
    • Serial Number: Number of days since December 30th 1899.
  • When Filter Has Multiple Matches: Set to Return All Matches to get multiple matches. By default only the first result gets returned.

First row

n8n treats the first row in a Google Sheet as a heading row, and doesn't return it when reading all rows. If you want to read the first row, use the Options to set Data Location on Sheet.

Refer to the Method: spreadsheets.batchUpdate | Google Sheets API documentation for more information.

Update Row#​

Use this operation to update existing row in a sheet. This operation only updates existing rows. To append rows when a matching entry isn't found in a sheet, use Append or Update Row operation instead.

Enter these parameters:

  • Credential to connect with: Create or select an existing Google Sheets credentials.
  • Resource: Select Sheet Within Document.
  • Operation: Select Update Row.
  • Document: Choose a spreadsheet with the sheet you want to update.
    • Select From list to choose the spreadsheet title from the dropdown list, By URL to enter the url of the spreadsheet, or By ID to enter the spreadsheetId.
    • You can find the spreadsheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
  • Sheet: Choose a sheet you want to update.
    • Select From list to choose the sheet title from the dropdown list, By URL to enter the url of the sheet, By ID to enter the sheetId, or By Name to enter the sheet title.
    • You can find the sheetId in a Google Sheets URL: https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId.
  • Mapping Column Mode:
    • Map Each Column Manually: Enter Values to Send for each column.
    • Map Automatically: n8n looks for incoming data that matches the columns in Google Sheets automatically. In this mode, make sure the incoming data fields are the same as the columns in Google Sheets. (Use an Edit Fields node before this node to change them if required.)
    • Nothing: Don't map any data.

Options#​

  • Cell Format: Use this option to choose how to format the data in cells. Refer to Google Sheets API | CellFormat for more information.
    • Let Google Sheets format (default): n8n formats text and numbers in the cells according to Google Sheets' default settings.
    • Let n8n format: New cells in your sheet will have the same data types as the input data provided by n8n.
  • Data Location on Sheet: Use this option when you need to specify where the data range on your sheet.
    • Header Row: Specify the row index that contains the column headers.
    • First Data Row: Specify the row index where the actual data starts.

Refer to the Method: spreadsheets.batchUpdate | Google Sheets API documentation for more information.

This page was

Thumbs upHelpful

Thumbs downNot helpful

Thanks for your feedback!

Submit

Back to top