# Zoho Sheet

> Online spreadsheets. Create, copy and share workbooks, manage worksheets and tables, read, add, update and delete rows as records, and read or write cells.

App · Spreadsheets · 20 actions. Web page: https://docs.lodol.com/docs/integrations/zoho-sheet

## Actions

### Add table records

Add rows to a table, at its end or its top.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Table (`table_name`, text, required): The table, by name, such as Table1.
- Row (`row_values`, object): One row to add: each column's header and its value. Use 'Rows' instead to add several.
- Rows (`json_data`, list): Optional. Several rows to add, each keyed by column header, e.g. [{"Name": "Joe", "Units": 284}, {"Name": "Beth", "Units": 290}]. Replaces 'Row' when given.
- Add at the top (`insert_at_top`, true/false): Optional. Add the rows above the others. Default: `false`.

**Outputs**: Where the rows were written.

- `end_column` (number): The last column written.
- `end_row` (number): The last row written.
- `records_added` (number): How many rows were sent.
- `sheet_name` (text): The worksheet written to.
- `start_column` (number): The first column written.
- `start_row` (number): The first row written.

**Example output**

```json
{
  "end_column": 3,
  "end_row": 11,
  "records_added": 2,
  "sheet_name": "Sheet1",
  "start_column": 1,
  "start_row": 10
}
```

### Add worksheet records

Add rows after a worksheet's last used row, matched to its header row. Up to 1,000 rows at a time.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.
- Row (`row_values`, object): One row to add: each column's header and its value. Use 'Rows' instead to add several.
- Rows (`json_data`, list): Optional. Several rows to add, each keyed by column header, e.g. [{"Name": "Joe", "Units": 284}, {"Name": "Beth", "Units": 290}]. Replaces 'Row' when given.
- Header row (`header_row`, number): Optional. The row holding the column names. Defaults to 1; set it when the data starts lower down. From 1 to 65536.

**Outputs**: Where the rows were written.

- `end_column` (number): The last column written.
- `end_row` (number): The last row written.
- `records_added` (number): How many rows were sent.
- `sheet_name` (text): The worksheet written to.
- `start_column` (number): The first column written.
- `start_row` (number): The first row written.

**Example output**

```json
{
  "end_column": 3,
  "end_row": 11,
  "records_added": 2,
  "sheet_name": "Sheet1",
  "start_column": 1,
  "start_row": 10
}
```

### Copy a workbook

Make a copy of a workbook, such as a template to fill in.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Name of the copy (`workbook_name`, text): Optional. Defaults to Zoho's 'Copy of ...' name.
- Folder ID (`parent_id`, text): Optional. The id of the Zoho WorkDrive folder to put the copy in. Defaults to My Folder.
- Keep locked ranges (`copy_lock_settings`, true/false): Optional. Copy the workbook's locked ranges and worksheets too. Default: `true`.

**Outputs**: The copy.

- `resource_id` (text): The copy's workbook id.
- `source_resource_id` (text): The id of the workbook that was copied.
- `workbook_name` (text): The copy's name.
- `workbook_url` (text): The copy's address in Zoho Sheet.

**Example output**

```json
{
  "resource_id": "aaaaabbbbbccccdddddfffff",
  "source_resource_id": "aaaaabbbbbccccdddddeeeee",
  "workbook_name": "Copy of Test",
  "workbook_url": "https://sheet.zoho.com/sheet/open/aaaaabbbbbccccdddddfffff"
}
```

### Create a table

Turn a range of a worksheet into a table, so its rows can be read and written as records.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.
- First row (`start_row`, number, required): The range's first row. From 1 to 65536.
- First column (`start_column`, text, required): The range's first column, as a number or a letter (1 or A).
- Last row (`end_row`, number, required): The range's last row. From 1 to 65536.
- Last column (`end_column`, text, required): The range's last column, as a number or a letter (3 or C).
- First row is the header (`contains_header`, true/false): Optional. Off adds a header row above the range instead. Default: `true`.
- Header names (`header_names`, list): Optional. One different name per column, such as Phone, Email.

**Outputs**: The new table.

- `table_id` (number): The table's id.
- `table_name` (text): The table's name, e.g. Table1.

**Example output**

```json
{
  "table_id": 1,
  "table_name": "Table1"
}
```

### Create a workbook

Create an empty workbook with one worksheet, in My Folder of Zoho WorkDrive unless a folder is given.

**Inputs**

- Name (`workbook_name`, text, required): The new workbook's name.
- Folder ID (`parent_id`, text): Optional. The id of the Zoho WorkDrive folder to create it in. Defaults to My Folder.

**Outputs**: The new workbook.

- `resource_id` (text): The workbook's id, used by every other Zoho Sheet action.
- `workbook_name` (text): The workbook's name.
- `workbook_url` (text): The workbook's address in Zoho Sheet.
- `worksheet_id` (text): The id of its first worksheet, e.g. 0#.
- `worksheet_name` (text): The name of its first worksheet, e.g. Sheet1.

**Example output**

```json
{
  "resource_id": "aaaaabbbbbccccdddddeeeee",
  "workbook_name": "Sheet1",
  "workbook_url": "https://sheet.zoho.com/sheet/open/aaaaabbbbbccccdddddeeeee",
  "worksheet_id": "0#",
  "worksheet_name": "Sheet1"
}
```

### Create a worksheet

Add a worksheet (tab) to a workbook.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Name (`worksheet_name`, text): Optional. Up to 31 characters. Defaults to the next free SheetN name.

**Outputs**: The new worksheet and all the workbook's worksheets.

- `new_worksheet_name` (text): The new worksheet's name, as Zoho reports it.
- `worksheet_id` (text): The new worksheet's id.
- `worksheet_name` (text): The new worksheet's name.
- `worksheet_names` (list): Every worksheet, in order.

**Example output**

```json
{
  "new_worksheet_name": "Sheet3",
  "worksheet_id": "2#",
  "worksheet_name": "Sheet3",
  "worksheet_names": [
    {
      "worksheet_id": "0#",
      "worksheet_name": "Sheet1"
    },
    {
      "worksheet_id": "2#",
      "worksheet_name": "Sheet3"
    }
  ]
}
```

### Delete a worksheet

Delete a worksheet and everything on it. A workbook's last worksheet cannot be deleted.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.

**Outputs**: The worksheets left.

- `deleted_worksheet_name` (text): The worksheet that was deleted.
- `worksheet_names` (list): The remaining worksheets.

**Example output**

```json
{
  "deleted_worksheet_name": "Sheet3",
  "worksheet_names": [
    {
      "worksheet_id": "0#",
      "worksheet_name": "Sheet1"
    }
  ]
}
```

### Delete worksheet records

Delete the worksheet rows that match a condition, or the rows at given numbers.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.
- Condition (`criteria`, text): Optional. Which rows, such as "Month"="March" or "Amount">50. Compare with =, !=, >, < or contains, and join up to five with and / or, e.g. ("Month"="March" or "Month"="April") and "Amount">30.
- Row numbers (`row_array`, list): Optional. Delete these rows instead of using a condition, such as 2, 3, 5.
- Remove the rows (`delete_rows`, true/false): Optional. On removes the rows and moves the rows below up. Off only clears their cells. Default: `true`.
- First match only (`first_match_only`, true/false): Optional. Change only the first row that matches. Default: `false`.
- Match case (`is_case_sensitive`, true/false): Optional. Whether the condition matches upper and lower case exactly. Default: `true`.
- Header row (`header_row`, number): Optional. The row holding the column names. Defaults to 1; set it when the data starts lower down. From 1 to 65536.

**Outputs**: How many rows were deleted.

- `no_of_rows_deleted` (number): How many rows were deleted or erased.
- `no_of_rows_remaining` (number): How many matching rows Zoho left for another call; run the step again when this is above 0.

**Example output**

```json
{
  "no_of_rows_deleted": 5,
  "no_of_rows_remaining": 0
}
```

### Find cells

Find the cells containing some text, in the whole workbook, one worksheet, one row or one column.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Text (`search`, text, required): The text to look for.
- Search in (`search_scope`, text): Optional. Defaults to the whole workbook. One of: `workbook`, `worksheet`, `row`, `column`. Default: `workbook`.
- Worksheet (`worksheet_name`, text): Required unless searching the whole workbook.
- Row (`row`, number): Required when searching a row: its number. From 1 to 65536.
- Column (`column`, text): Required when searching a column: its number or letter.
- Match case (`is_case_sensitive`, true/false): Optional. Match upper and lower case exactly. Default: `false`.
- Whole cell only (`is_exact_match`, true/false): Optional. Match only cells whose whole content is the text. Default: `false`.

**Outputs**: The matching cells.

- `cells` (list): The matching cells.
- `matches_found` (number): How many cells matched.

**Example output**

```json
{
  "cells": [
    {
      "column_index": 1,
      "content": "jack",
      "row_index": 1,
      "worksheet_id": "1#",
      "worksheet_name": "test2"
    }
  ],
  "matches_found": 1
}
```

### Get range content

Read the non-empty cells of a range, row by row.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.
- First row (`start_row`, number, required): The range's first row. From 1 to 65536.
- First column (`start_column`, text, required): The range's first column, as a number or a letter (1 or A).
- Last row (`end_row`, number, required): The range's last row. From 1 to 65536.
- Last column (`end_column`, text, required): The range's last column, as a number or a letter (3 or C).

**Outputs**: The range's non-empty cells.

- `range_details` (list): Each row that has content; empty rows are left out.

**Example output**

```json
{
  "range_details": [
    {
      "row_details": [
        {
          "column_index": 3,
          "content": "d"
        }
      ],
      "row_index": 2
    }
  ]
}
```

### Get table records

Read a table's rows as records keyed by column header, optionally only those matching a condition.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Table (`table_name`, text, required): The table, by name, such as Table1.
- Where column (`criteria_column`, text): Optional. The header of the column to test.
- Is (`criteria_operator`, text): Optional. How to compare. Defaults to equals. One of: `EQUALS`, `NOT_EQUALS` (does not equal), `CONTAINS`, `NOT_CONTAINS` (does not contain), `GREATER_THAN` (is greater than), `LESS_THAN` (is less than), `IS_EMPTY` (is empty), `IS_NOT_EMPTY` (is not empty).
- Value (`criteria_value`, text): Optional. What to compare with (not needed for empty tests).
- Compare as (`criteria_type`, text): Optional. Text, a date or a number. Dates and numbers support only equals, does not equal, greater than and less than. One of: `String`, `Date`, `Number`.
- Conditions (advanced) (`criteria_json`, list): Optional. Up to five conditions, replacing the one above, e.g. [{"key": "Month", "operator": "EQUALS", "matcher": "March"}, {"key": "Amount", "operator": "GREATER_THAN", "matcher": 50, "type": "Number"}]. Operators: EQUALS, NOT_EQUALS, CONTAINS, NOT_CONTAINS, GREATER_THAN, LESS_THAN, IS_EMPTY, IS_NOT_EMPTY, IN, NOT_IN.
- Combine conditions as (`criteria_pattern`, text): Required with more than one condition: how they combine, by position, such as 1 AND 2 or (1 OR 2) AND 3.
- Columns (`column_names`, list): Optional. Only these columns, by header. Leave empty for all.
- Values as (`render_option`, text): Optional. How cell values come back. Defaults to as shown. One of: `formatted` (As shown (formatted)), `unformatted` (Raw values), `formula` (Formulas).
- Maximum records (`count`, number): Optional. At most this many records. From 1 to 65536.
- Match case (`is_case_sensitive`, true/false): Optional. Whether the condition matches upper and lower case exactly. Default: `true`.

**Outputs**: The table's matching rows.

- `records` (list): The rows.
- `records_count` (number): How many rows matched.

**Example output**

```json
{
  "records": [
    {
      "Amount": 60,
      "Item": "Pen",
      "Month": "March",
      "Source": "Anderson",
      "row_index": 1
    },
    {
      "Amount": 40,
      "Item": "Pencil",
      "Month": "April",
      "Source": "Gill",
      "row_index": 2
    }
  ],
  "records_count": 2
}
```

### Get worksheet records

Read a worksheet's rows as records keyed by the header row, optionally only those matching a condition.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.
- Condition (`criteria`, text): Optional. Which rows, such as "Month"="March" or "Amount">50. Compare with =, !=, >, < or contains, and join up to five with and / or, e.g. ("Month"="March" or "Month"="April") and "Amount">30.
- Columns (`column_names`, list): Optional. Only these columns, by header. Leave empty for all.
- Values as (`render_option`, text): Optional. How cell values come back. Defaults to as shown. One of: `formatted` (As shown (formatted)), `unformatted` (Raw values), `formula` (Formulas).
- Start at record (`records_start_index`, number): Optional. Skip to this record (1 is the first). From 1 to 65536.
- Maximum records (`count`, number): Optional. At most this many records. From 1 to 65536.
- Header row (`header_row`, number): Optional. The row holding the column names. Defaults to 1; set it when the data starts lower down. From 1 to 65536.
- Match case (`is_case_sensitive`, true/false): Optional. Whether the condition matches upper and lower case exactly. Default: `true`.

**Outputs**: The worksheet's matching rows.

- `records` (list): The rows.
- `records_count` (number): How many rows matched.
- `records_end_index` (number): The position just after the last row returned.
- `records_start_index` (number): The position of the first row returned.

**Example output**

```json
{
  "records": [
    {
      "Amount": 60,
      "Item": "Pen",
      "Month": "March",
      "Source": "Anderson",
      "row_index": 1
    },
    {
      "Amount": 40,
      "Item": "Pencil",
      "Month": "April",
      "Source": "Gill",
      "row_index": 2
    }
  ],
  "records_count": 2,
  "records_end_index": 3,
  "records_start_index": 1
}
```

### List tables

List a workbook's tables and the cells each covers.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).

**Outputs**: The workbook's tables.

- `tables` (list): The tables.

**Example output**

```json
{
  "tables": [
    {
      "end_column": 5,
      "end_row": 10,
      "start_column": 1,
      "start_row": 1,
      "table_id": 1,
      "table_name": "Table1"
    }
  ]
}
```

### List workbooks

List the workbooks the account owns or has been shared.

**Inputs**

- Sort by (`sort_option`, text): Optional. Defaults to recently created. One of: `recently_opened` (Recently opened), `recently_modified` (Recently changed), `recently_created` (Recently created), `ascending` (Name, A to Z), `descending` (Name, Z to A).
- Start at (`start_index`, number): Optional. Skip to this position in the list (1 is the first). At least 1.
- Maximum workbooks (`count`, number): Optional. At most this many. Zoho's default is 1000. From 1 to 1000.

**Outputs**: Workbooks the account owns or has been shared.

- `resource_count` (number): How many workbooks there are in all.
- `resource_end_index` (number): The position of the last workbook listed.
- `resource_start_index` (number): The position of the first workbook listed.
- `workbooks` (list): The workbooks.

**Example output**

```json
{
  "resource_count": 1,
  "resource_end_index": 1,
  "resource_start_index": 1,
  "workbooks": [
    {
      "created_by": "jack",
      "created_time": "Friday, 6 April, 2018 4:14:10 PM",
      "last_modified_time": "Friday, 6 April, 2018 4:14:10 PM",
      "resource_id": "aaaaabbbbbccccdddddeeeee",
      "workbook_name": "Test workbook",
      "workbook_url": "https://sheet.zoho.com/sheet/open/aaaaabbbbbccccdddddeeeee"
    }
  ]
}
```

### List worksheets

List a workbook's worksheets (tabs), in order.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).

**Outputs**: The workbook's worksheets.

- `worksheet_names` (list): The worksheets, in order.

**Example output**

```json
{
  "worksheet_names": [
    {
      "worksheet_id": "0#",
      "worksheet_name": "Sheet1"
    },
    {
      "worksheet_id": "2#",
      "worksheet_name": "Sheet3"
    }
  ]
}
```

### Move a workbook to the trash

Move a workbook to the trash. It can be restored from Zoho WorkDrive's trash.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).

**Outputs**: The workbook moved to the trash.

- `resource_id` (text): The trashed workbook's id.
- `trashed` (true/false): Always true: Zoho moved it to the trash.

**Example output**

```json
{
  "resource_id": "aaaaabbbbbccccdddddeeeee",
  "trashed": true
}
```

### Set a cell

Write a value or a formula (starting with =) to one cell.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.
- Row (`row`, number, required): The cell's row. From 1 to 65536.
- Column (`column`, text, required): The cell's column, as a number or a letter (2 or B).
- Content (`content`, text, required): What to write, such as 42, Paid or =SUM(B2:B9).

**Outputs**: The cell written.

- `cell` (object): The cell.

**Example output**

```json
{
  "cell": {
    "cell_value": "asd",
    "column_index": 4,
    "formula": "asd",
    "row_index": 4
  }
}
```

### Share a workbook

Give someone access to a workbook, change it, or remove it.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Email (`user_email`, text, required): The email address of the person to share with.
- Access (`access_level`, text, required): What they can do. Remove access takes it away. One of: `share` (Can share (view, comment, edit and share)), `edit` (Can edit), `view_and_comment` (Can view and comment), `view` (Can view), `remove_share` (Remove access). Default: `view`.

**Outputs**: The access given.

- `access_level` (text): share, edit, view_and_comment, view or remove_share.
- `resource_id` (text): The workbook's id.
- `shared` (true/false): False when the access was removed.
- `user_email` (text): Who it was shared with.

**Example output**

```json
{
  "access_level": "view",
  "resource_id": "aaaaabbbbbccccdddddeeeee",
  "shared": true,
  "user_email": "xyz@example.com"
}
```

### Update table records

Change columns on the table rows that match a condition.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Table (`table_name`, text, required): The table, by name, such as Table1.
- New values (`data`, object, required): The columns to change and their new values, keyed by the exact header text.
- Where column (`criteria_column`, text): Optional. The header of the column to test.
- Is (`criteria_operator`, text): Optional. How to compare. Defaults to equals. One of: `EQUALS`, `NOT_EQUALS` (does not equal), `CONTAINS`, `NOT_CONTAINS` (does not contain), `GREATER_THAN` (is greater than), `LESS_THAN` (is less than), `IS_EMPTY` (is empty), `IS_NOT_EMPTY` (is not empty).
- Value (`criteria_value`, text): Optional. What to compare with (not needed for empty tests).
- Compare as (`criteria_type`, text): Optional. Text, a date or a number. Dates and numbers support only equals, does not equal, greater than and less than. One of: `String`, `Date`, `Number`.
- Conditions (advanced) (`criteria_json`, list): Optional. Up to five conditions, replacing the one above, e.g. [{"key": "Month", "operator": "EQUALS", "matcher": "March"}, {"key": "Amount", "operator": "GREATER_THAN", "matcher": 50, "type": "Number"}]. Operators: EQUALS, NOT_EQUALS, CONTAINS, NOT_CONTAINS, GREATER_THAN, LESS_THAN, IS_EMPTY, IS_NOT_EMPTY, IN, NOT_IN.
- Combine conditions as (`criteria_pattern`, text): Required with more than one condition: how they combine, by position, such as 1 AND 2 or (1 OR 2) AND 3.
- First match only (`first_match_only`, true/false): Optional. Change only the first row that matches. Default: `false`.
- Match case (`is_case_sensitive`, true/false): Optional. Whether the condition matches upper and lower case exactly. Default: `true`.
- Update every row (`update_all_rows`, true/false): Turn on to update every row when no condition is given. Left off, a step without a condition is refused, so it cannot overwrite a whole sheet by accident. Default: `false`.

**Outputs**: How many rows changed.

- `no_of_affected_rows` (number): How many rows were updated.

**Example output**

```json
{
  "no_of_affected_rows": 2
}
```

### Update worksheet records

Change columns on the worksheet rows that match a condition.

**Inputs**

- Workbook (`resource_id`, text, required): The workbook. Pick one, or paste its id or its address (https://sheet.zoho.com/sheet/open/<id>).
- Worksheet (`worksheet_name`, text, required): The worksheet (tab), by name, such as Sheet1.
- New values (`data`, object, required): The columns to change and their new values, keyed by the exact header text.
- Condition (`criteria`, text): Optional. Which rows, such as "Month"="March" or "Amount">50. Compare with =, !=, >, < or contains, and join up to five with and / or, e.g. ("Month"="March" or "Month"="April") and "Amount">30.
- First match only (`first_match_only`, true/false): Optional. Change only the first row that matches. Default: `false`.
- Match case (`is_case_sensitive`, true/false): Optional. Whether the condition matches upper and lower case exactly. Default: `true`.
- Update every row (`update_all_rows`, true/false): Turn on to update every row when no condition is given. Left off, a step without a condition is refused, so it cannot overwrite a whole sheet by accident. Default: `false`.
- Header row (`header_row`, number): Optional. The row holding the column names. Defaults to 1; set it when the data starts lower down. From 1 to 65536.

**Outputs**: How many rows changed.

- `no_of_affected_rows` (number): How many rows were updated.

**Example output**

```json
{
  "no_of_affected_rows": 2
}
```