Google Sheets 文档内工作表操作#

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

此节点可用作 AI 工具

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

附加或更新行#

使用此操作更新现有行，或在工作表中未找到匹配项时在数据末尾添加新行。

输入以下参数：

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

Options#

单元格格式：使用此选项选择如何格式化单元格中的数据。有关更多信息，请参考 Google Sheets API | CellFormat。
- 让 Google Sheets 格式化（默认）：n8n 根据 Google Sheets 的默认设置格式化单元格中的文本和数字。
- 让 n8n 格式化：工作表中的新单元格将具有与 n8n 提供的输入数据相同的数据类型。
工作表上的数据位置：当您需要指定工作表上的数据范围时使用此选项。
- 标题行：指定包含列标题的行索引。
- 第一个数据行：指定实际数据开始的行索引。
处理输入中的额外字段：当使用 列映射模式 > 自动映射 时，使用此选项决定如何处理输入数据中与工作表现有列不匹配的字段。
- 插入到新列中（默认）：为任何额外数据添加新列。
- 忽略它们：忽略与现有列不匹配的额外数据。
- 错误：抛出错误并停止执行。
使用追加：启用此选项以使用 Google API 追加端点添加新的数据行。
- 默认情况下，n8n 会追加空行或列，然后添加新数据。这种方法可以确保数据对齐，但可能效率较低。使用追加端点可以通过最小化 API 调用次数和简化流程来获得更好的性能。但是，如果现有工作表数据存在不一致（如行列之间的间隙或中断），n8n 可能会将新数据添加到错误的位置，导致对齐问题。
- 当性能是优先考虑且工作表中的数据结构一致且没有间隙时，请使用此选项。

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

附加行#

使用此操作在工作表数据末尾附加新行。

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#

单元格格式：使用此选项选择如何格式化单元格中的数据。有关更多信息，请参考 Google Sheets API | CellFormat。
- 让 Google Sheets 格式化（默认）：n8n 根据 Google Sheets 的默认设置格式化单元格中的文本和数字。
- 让 n8n 格式化：工作表中的新单元格将具有与 n8n 提供的输入数据相同的数据类型。
工作表上的数据位置：当您需要指定工作表上的数据范围时使用此选项。
- 标题行：指定包含列标题的行索引。
- 第一个数据行：指定实际数据开始的行索引。
处理输入中的额外字段：当使用 列映射模式 > 自动映射 时，使用此选项决定如何处理输入数据中与工作表现有列不匹配的字段。
- 插入到新列中（默认）：为任何额外数据添加新列。
- 忽略它们：忽略与现有列不匹配的额外数据。
- 错误：抛出错误并停止执行。
使用追加：启用此选项以使用 Google API 追加端点添加新的数据行。
- 默认情况下，n8n 会追加空行或列，然后添加新数据。这种方法可以确保数据对齐，但可能效率较低。使用追加端点可以通过最小化 API 调用次数和简化流程来获得更好的性能。但是，如果现有工作表数据存在不一致（如行列之间的间隙或中断），n8n 可能会将新数据添加到错误的位置，导致对齐问题。
- 当性能是优先考虑且工作表中的数据结构一致且没有间隙时，请使用此选项。

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

清除工作表#

使用此操作清除工作表中的所有数据。

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.

创建新工作表#

使用此操作创建新工作表。

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.

删除工作表#

使用此操作永久删除工作表。

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.

删除行或列#

使用此操作删除工作表中的行或列。

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.

获取行#

使用此操作从工作表中读取一行或多行数据。

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.

更新行#

使用此操作更新工作表中的现有行。此操作仅更新现有行。要在工作表中未找到匹配项时附加行，请使用附加或更新行操作。

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.

此页面是否

💬 微信

🚀 与作者交流

关注公众号

n8n实战笔记
📚 教程 💡 案例 🔧 技巧

添加微信

1对1 专业指导
⚡ 快答 🎯 定制 🚀 支持

添加后请直说来意，看到会回复。谢谢