Introduction

Each spreadsheet document you want to access must be configured in the SHEETS2API panel as an API. After creating the API, you’ll receive a unique endpoint url. You can send RESTful requests to read, create, update or delete rows. The rest of the documentation explains how each request works and what you can expect in a response.

Installation

You can use one of our Libraries:

Spreadsheet Example

First row of your spreadsheet should contain the column names. Each next row will be treated as a row value (You should make first spreadsheet row for column names).

See Example of how we get spreadsheet data as a HTML Table:

Id Name Email Country

HTTP Status Codes

Here is the list of available response codes:

Code Meaning
200 The request has been succeeded.
404 The data you are looking for is not found or cannot be reached.
401 An error with authorization using a Google account or incorrect credentials for API if Basic Auth is enabled.
403 You don't have permission to make changes to spreadsheet usually this happens when your spreadsheet is (view only).

SHEETS2API API

GET - All Data

GET http://sheets2api.com/api/v1/?apiId=617989cb51352

Returns an array with all data from the spreadsheet as a JSON.

Tip: There some additional query procedures to apply to your request below:

  • limit: How many rows to return from your Spreadsheet.
  • offset: Which row to start from.
  • order: sort in asc (ascending) or desc (descending) order.
  • orderBy: the column you want to sort by.

Note: Those parameters mentioned above are optional and can be excluded from the request.

Example:

fetch('http://sheets2api.com/api/v1/?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/?apiId=617989cb51352', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

[
    {
        "Id": "1",
        "Name": "john",
        "Email": "john@gmail.com",
        "Country": "Netherlands"
    },
    {
        "Id": "2",
        "Name": "brad",
        "Email": "brad@gmail.com",
        "Country": "NewYork"
    },
    {
        "Id": "3",
        "Name": "homer",
        "Email": "homer@gmail.com",
        "Country": "Canada"
    },
    {
        "Id": "5",
        "Name": "Ranger",
        "Email": "ranger@gmail.com",
        "Country": "Alabama"
    }
]

GET - Keys (Column names)

GET http://sheets2api.com/api/v1/keys?apiId=617989cb51352

Return an array of keys (column names) of the spreadsheet.

Example:
fetch('http://sheets2api.com/api/v1/keys?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/keys?apiId=617989cb51352', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/keys?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

[
	"Id",
	"Name",
	"Email",
	"Country"
]

Tip: If you want to get keys (column names) of a specific Sheet you'll need to send request to the following api call.

GET http://sheets2api.com/api/v1/sheets/keys?apiId=617989cb51352&sheet=Sheet1

Response:

[
	"Id",
	"Name",
	"Email",
	"Country"
]

Now We got keys (Column names) of Sheet1.

Note: Don't forget to specify sheet=YOUR_SHEET_NAME in your api call.

GET - Spreadsheet Name

GET http://sheets2api.com/api/v1/name?apiId=617989cb51352

Returns the name of the Spreadsheet.

Example:
fetch('http://sheets2api.com/api/v1/name?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/name?apiId=617989cb51352', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/name?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

{
    "name": "XLS SHEETS Test Document"
}

GET - List of Sheets

GET http://sheets2api.com/api/v1/sheets?apiId=617989cb51352

Returns a list of sheets (tabs).

Example:
fetch('http://sheets2api.com/api/v1/sheets?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/sheets?apiId=617989cb51352', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/sheets?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

{
	"sheets": 
	[
		{
			"title": "Sheet1",
			"id": 0
		},
		{
			"title": "Sheet2",
			"id": 1421161285
		},
		{
			"title": "Sheet3",
			"id": 146255848
		}
	]
}

GET - Row Count

GET http://sheets2api.com/api/v1/count?apiId=617989cb51352

Returns the number of rows that has data in the spreadsheet (not the empty rows).

Note: First row that has (column names) is not counted.

Example:
fetch('http://sheets2api.com/api/v1/count?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/count?apiId=617989cb51352', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/count?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

{
    "rows": 4
}

GET - Search in The Spreadsheet

Search by Query:

GET http://sheets2api.com/api/v1/search?apiId=617989cb51352&query=brad,john

Returns an array of rows as objects matching the search query.

If you want to search for multiple rows you can separate the search query values by comma(,). However the search query works for both (single/multiple) values.

Note: Rows returned are ordered the same as search query.

For Example: if the search query=brad,john it will return rows at the same order you validated your search values like the response below.

Example:
fetch('http://sheets2api.com/api/v1/search?apiId=617989cb51352&query=brad,john')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/search?apiId=617989cb51352&query=brad,john', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/search?apiId=617989cb51352&query=brad,john')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

{
    "0": {
        "Id": "2",
        "Name": "brad",
        "Email": "brad@gmail.com",
        "Country": "NewYork"
    },
    "2": {
        "Id": "1",
        "Name": "john",
        "Email": "john@gmail.com",
        "Country": "Netherlands"
    }
}

Note: Same as getting keys (Column names), if you want to search in a specific Sheet then use the following api call.

GET http://sheets2api.com/api/v1/sheets/search?apiId=617989cb51352&sheet=Sheet1&query=homer,brad

Response something like this:

{
    "0": {
        "Id": "3",
        "Name": "homer",
        "Email": "homer@gmail.com",
        "Country": "Canada"
    },
    "2": {
        "Id": "2",
        "Name": "brad",
        "Email": "brad@gmail.com",
        "Country": "NewYork"
    }
}

Now We got search results rows of Sheet1.

Notice: That we included sheet=Sheet1 and query=YOUR_SEARCH_STRING_SEPARATED_BY_COMMA.

Search by Keys (Column names):

GET http://sheets2api.com/api/v1/searchBy?apiId=617989cb51352&Id=1&Name=john

Returns an array of rows objects that match parameters.

Specify column name and value, However to search for specific row you need to make sure your parameters are unique in your spreadsheet.

For Example:

fetch('http://sheets2api.com/api/v1/searchBy?apiId=617989cb51352&Id=1&Name=john')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/searchBy?apiId=617989cb51352&Id=1&Name=john', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/searchBy?apiId=617989cb51352&Id=1&Name=john')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

[
    {
        "Id": "1",
        "Name": "john",
        "Email": "john@gmail.com",
        "Country": "Netherlands"
    }
]

Additional Parameters:

  • caseSensitive: Search in spreadsheet with case sensitive to uppercase, lowercase or capitalized strings. This parameter can take true or false, if not specified or empty the request will use case sensitive as true.

GET - SPREADSHEETS LIST

GET http://sheets2api.com/api/v1/list?apiId=617989cb51352

Returns a list of all spreadsheets for a user account.

Send a request with an apiId parameter.

Example Request:

fetch('http://sheets2api.com/api/v1/list?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/list?apiId=617989cb51352', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/list?apiId=617989cb51352')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

In the response you'll get an array of objects, each object in the array contains spreadsheet info like (id, name...).

Try it using our API Explorer Tool

POST - Add Row/Rows to Spreadsheet

POST http://sheets2api.com/api/v1/addRow

You can append (Insert) row to your Spreadsheet by sending a request with array data contains the data to insert, this array must be called data. and also validating an apiId.

Note: Data will be appended after the last row (not empty row) of the Spreadsheet.

Example:

Your request body should look like this:

{
    "data":
        [
            "5",
            "User1",
            "user1@gmail.com",  
            "Germany"
        ],
    "apiId": "YOUR_API_ID"
}

POST http://sheets2api.com/api/v1/addRows

Use this api call to append multiple rows at once in the same request, you will have to send data using array of arrays (nested arrays) each array is actually a row of data.

Request Body:

{
    "data": [
        [
            "5",
            "User1",
            "user1@gmail.com",  // Row number 1
            "Germany"
        ],
        [
            "6",
            "User2",
            "user2@gmail.com",	// Row number 2
            "Russia"
        ]
    ],
    "apiId": "YOUR_API_ID"
}

Tip: You can always test api requests using our API Explorer Tool.

If the request succeeds you'll get response like this:

{
    "success": "This operation has been completed successfully!",
    "code": 200,
    "affected": {
        "range": "Sheet1!A2:Z",
        "sheet": {
            "properties": {
                "hidden": null,
                "index": 0,
                "rightToLeft": null,
                "sheetId": 0,
                "sheetType": "GRID",
                "title": "Sheet1",
                "gridProperties": {
                    "columnCount": 26,
                    "columnGroupControlAfter": null,
                    "frozenColumnCount": null,
                    "frozenRowCount": null,
                    "hideGridlines": null,
                    "rowCount": 1000,
                    "rowGroupControlAfter": null
                }
            }
        }
    },
    "spreadsheet": {
        "spreadsheetId": "1t59SEiPQtySWFwwBEzP5jcgmX18Ywc5ytYiitJYxbY8",
        "tableRange": "Sheet1!A1:D6",
        "updates": {
            "spreadsheetId": "1t59SEiPQtySWFwwBEzP5jcgmX18Ywc5ytYiitJYxbY8",
            "updatedCells": 4,
            "updatedColumns": 4,
            "updatedRange": "Sheet1!A7:D7",
            "updatedRows": 1
        }
    }
}

POST - Update Spreadsheet

POST - http://sheets2api.com/api/v1/update

Update a row using conditional request, simply specify column and value of that column to create if condition, also you need to specify data to replace old values using an object like the following example:

Request Body:

{
    "column": "Id",  // We are looking for column Id
    "value": "4",    // With the value of 4
    "data": {
        "Name": "rico",  // data to replace old data
        "Age": "60"
    },
    "apiId": "YOUR_API_ID"
}
Response:
{
    "spreadsheetId": "1NavOtgd_VFPctKydY0MZvjYTyQjF6hKczIa_pmBF2HA",
    "query": "Id=4",
    "sheet": "Users",
    "affected": {
        "columns": 2
    },
    "updatedColumns": [
        "Name",
        "Age"
    ]
}

In the response you'll get the columns that has been affected by the Update request along with Sheet name and the number of columns.

Note: Be aware of using this api call with the wrong condition query can result of data loss.

POST - Delete Spreadsheet Row

POST http://sheets2api.com/api/v1/deleteRows

Delete one row or more depending on how you made the condition in the api call.

This api call same as Update Spreadsheet request, you neel to specify a condition (column and value) to let the api search for that column and delete the whole row that match that condition.

Tip: Always use unique columns and values to make row deletion be exact and not jumping on any other rows.

Request Body:

{
    "column": "Id",  // Make sure this column 
    "value": "3", // is Unique
    "apiId": "YOUR_API_ID"
}

Response:

{
    "spreadsheetId": "1NavOtgd_VFPctKydY0MZvjYTyQjF6hKczIa_pmBF2HA",
    "query": "Id=3",
    "sheet": "Users",
    "affected": {
        "rows": 1
    }
}

Tip: If you specify unknown column value you'll receive 0 rows affected.

Response with no rows deleted:

{
    "spreadsheetId": "1NavOtgd_VFPctKydY0MZvjYTyQjF6hKczIa_pmBF2HA",
    "query": "Id=10",
    "sheet": "Users",
    "affected": {
        "rows": 0
    }
}

Single Cells

GET - CELLS VALUES

GET http://sheets2api.com/api/v1/cells?apiId=617989cb51352&query=A1

Returns row of values for each cell specified in the request query.

Example Request:

fetch('http://sheets2api.com/api/v1/cells?apiId=617989cb51352&query=A1')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/cells?apiId=617989cb51352&query=A1', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/cells?apiId=617989cb51352&query=A1')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

[
    {
        "A1": "Id"
    }
]

Tip: You can search for multiple cells, simply separate them by comma (,).

For Example: let's search for 6 cells instead of one, we use the following api call:

GET http://sheets2api.com/api/v1/cells?apiId=617989cb51352&query=A1,B1,C1,A2,B2,C2

Response:

[
    {
        "A1": "Id",
        "B1": "Name",
        "C1": "Email",
        "A2": "1",
        "B2": "john",
        "C2": "john@gmail.com"
    }
]

GET - Sheet CELLS VALUES

GET http://sheets2api.com/api/v1/sheets/cells?apiId=617989cb51352&sheet=Sheet2&query=A2,C2

Returns row of values for each cell in the selected Sheet specified in the request.

Example Request:

fetch('http://sheets2api.com/api/v1/sheets/cells?apiId=617989cb51352&sheet=Sheet2&query=A2,C2')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/sheets/cells?apiId=617989cb51352&sheet=Sheet2&query=A2,C2', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/sheets/cells?apiId=617989cb51352&sheet=Sheet2&query=A2,C2')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

[
    {
        "A2": "1",
        "C2": "rick@gmail.com"
    }
]

Multiple Sheets (Tabs)

Get - All Data from a Sheet (Tab)

GET http://sheets2api.com/api/v1/sheets/sheet?apiId=617989cb51352&name=Sheet1

Returns all data from the specified Sheet in the request.

Optional Parameters:

  • limit: How many rows to return from your Sheet.
  • offset: Which row to start from.
  • order: sort in asc (ascending) or desc (descending) order.
  • orderBy: the column you want to sort by.
fetch('http://sheets2api.com/api/v1/sheets/sheet?apiId=617989cb51352&name=Sheet1')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/sheets/sheet?apiId=617989cb51352&name=Sheet1', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/sheets/sheet?apiId=617989cb51352&name=Sheet1')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

[
    {
        "Id": "1",
        "Name": "john",
        "Email": "john@gmail.com",
        "Country": "Netherlands"
    },
    {
        "Id": "2",
        "Name": "brad",
        "Email": "brad@gmail.com",
        "Country": "NewYork"
    },
    {
        "Id": "3",
        "Name": "homer",
        "Email": "homer@gmail.com",
        "Country": "Canada"
    },
    {
        "Id": "5",
        "Name": "Ranger",
        "Email": "ranger@gmail.com",
        "Country": "Alabama"
    },
    {
        "Id": "6",
        "Name": "User1",
        "Email": "user1@gmail.com",
        "Country": "Germany"
    }
]

GET - SEARCH DATA FROM A SHEET (TAB)

GET http://sheets2api.com/api/v1/sheets/searchBy?apiId=617989cb51352&sheet=Sheet2&Id=1&Name=rick

Returns rows of data matching your search parameters.

Optional Parameters:

  • caseSensitive: To search for values with lowercase, uppercase or capitalized, or simply ignore all that, can take true or false, if not specified it will automatically be false by default.

Example Request:

fetch('http://sheets2api.com/api/v1/sheets/searchBy?apiId=617989cb51352&sheet=Sheet2&Id=1&Name=rick')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/sheets/searchBy?apiId=617989cb51352&sheet=Sheet2&Id=1&Name=rick', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/sheets/searchBy?apiId=617989cb51352&sheet=Sheet2&Id=1&Name=rick')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

[
    {
        "Id": "1",
        "Name": "rick",
        "Email": "rick@gmail.com",
        "Country": "Ukraine"
    }
]

GET - SHEET ROW COUNT

GET http://sheets2api.com/api/v1/sheets/count?apiId=617989cb51352&sheet=Sheet1

Returns the number of rows that has data (first row excluded) in the selected Sheet.

Example Request:

fetch('http://sheets2api.com/api/v1/sheets/count?apiId=617989cb51352&sheet=Sheet1')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});
$options = [
    'http' => [
        'method' => 'GET'
    ]
];

$response = json_decode(
    file_get_contents('http://sheets2api.com/api/v1/sheets/count?apiId=617989cb51352&sheet=Sheet1', false, stream_context_create($options))
);
fetch('http://sheets2api.com/api/v1/sheets/count?apiId=617989cb51352&sheet=Sheet1')
	.then(response => response.json())
	.then(data => {
	    console.log(data);
	})
	.catch(error => {
	    console.error(error);
	});

Response:

{
    "rows": 5
}

POST - CREATE NEW SHEET (Tab)

POST http://sheets2api.com/api/v1/sheets/create

You need to specify sheet name and apiId parameters.

Example Request Body:

{
    "name": "SHEET_NAME_TO_CREATE",
    "apiId": "YOUR_API_ID"
}

If success, it will return the newly created Sheet Object.

Response:

{
    "spreadsheetId": "HIDDEN_SPREADSHEET_ID",
    "sheet": {
        "title": "Random_Sheet",
        "id": 849233654
    }
}

POST - DELETE SHEET (Tab)

POST http://sheets2api.com/api/v1/sheets/remove

You need to specify sheet names array (at least one sheet name in array) and apiId parameters.

Example Request Body:

{
    "names": ["Sheet1","Sheet2","Sheet3"], // You can specify only one sheet like  "names": ["Sheet1"]
    "apiId": "YOUR_API_ID"
}

Response:

{
    "spreadsheetId": "1NavOtgd_VFPctKydY0MZvjYTyQjF6hKczIa_pmBF2HA",
    "deletedSheets": {
        "0": [
            {
                "name": "Sheet1",
                "id": 1616740877
            }
        ],
        "1": [
            {
                "name": "Sheet2",
                "id": 632905725
            }
        ],
        "2": [
            {
                "name": "Sheet3",
                "id": 23215649
            }
        ],
        "count": 3
    }
}

POST - Delete Sheet Row

POST http://sheets2api.com/api/v1/sheets/deleteRows

Delete one row or more from a specific Sheet depending on how you made the condition in the api call.

This api call same as Update Sheet Row request, you neel to specify a condition (column and value) to let the api search for that column and delete the whole row that match that condition.

Tip: Always use unique columns and values to make row deletion be exact and not jumping on any other rows.

Request Body:

{
    "sheet": "Sheet7", // Sheet name to delete from
    "column": "Id",
    "value": "3",
    "apiId": "YOUR_API_ID"
}

Response:

{
    "spreadsheetId": "1NavOtgd_VFPctKydY0MZvjYTyQjF6hKczIa_pmBF2HA",
    "query": "Id=3",
    "sheet": "Sheet7",
    "affected": {
        "rows": 1
    }
}

Tip: If you specify unknown column value you'll receive 0 rows affected.

Response with no rows deleted:

{
    "spreadsheetId": "1NavOtgd_VFPctKydY0MZvjYTyQjF6hKczIa_pmBF2HA",
    "query": "Id=10",
    "sheet": "Sheet7",
    "affected": {
        "rows": 0
    }
}

POST - Update Sheet Row

POST - http://sheets2api.com/api/v1/sheets/update

Update a row using conditional request, simply specify column and value of that column to create if condition, also you need to specify data to replace old values using an object and Sheet name like the following example:

Request Body:

{
    "column": "Id",
    "value": "2",
    "sheet": "Sheet7", // Sheet name to update
    "data": {
        "Name": "rico",
        "Age": "60",
        "Email": "rico@yahoo.com"
    },
    "apiId": "YOUR_API_ID"
}
Response:
{
    "spreadsheetId": "1NavOtgd_VFPctKydY0MZvjYTyQjF6hKczIa_pmBF2HA",
    "query": "Id=2",
    "sheet": "Sheet7",
    "affected": {
        "columns": 3
    },
    "updatedColumns": [
        "Name",
        "Age",
        "Email"
    ]
}

In the response you'll get the columns that has been affected by the Update request along with Sheet name and the number of columns.

Note: Be aware of using this api call with the wrong condition query can result of data loss.

POST Add Sheet Row/Rows

POST http://sheets2api.com/api/v1/sheets/addRow

Use this api call to add one row to the end of your sheet (Tab).

You need to specify sheet name, apiId and data array containing the data row to append.

Request Body:

{ 
    "sheet":  "Sheet7",
    "data": [
    	"1",
    	"John",
    	"john@gmail.com",
    	"Canada"
    ],
    "apiId": "YOUR_API_ID"
}

POST http://sheets2api.com/api/v1/sheets/addRows

This api call will add multiple rows at once into your selected sheet.

You need to specify sheet name, apiId and data array contains nested arrays, each array inside data main array is actually a row to append into your sheet.

Request Body:

{ 
    "sheet":  "Sheet7",
    "data": [
        [
            "5",
            "User1",
            "user1@gmail.com",  // Row number 1
            "Germany"
        ],
        [
            "6",
            "User2",
            "user2@gmail.com",	// Row number 2
            "Russia"
        ]
    ],
    "apiId": "YOUR_API_ID"
}

POST - CLEAR SHEET

POST http://sheets2api.com/api/v1/sheets/clear

Clear all data from a Sheet including Keys (Column names).

You need to specify sheet name that you want to clear data from using sheet=YOUR_SHEET parameter.

Request body:

{
    "sheet":  "Sheet7",
    "apiId": "YOUR_API_ID"
}

Response:

{
    "clearedRange": "Sheet7!A1:Z1000",
    "spreadsheetId": "HIDDEN_SPREADSHEET_ID"
}

Now that we successfully cleared data from our Sheet, we receive in the response the cleared range along with our spreadsheet id.

Note: Be Careful while using this request in order not to accidentaly clear your hole data as this operation has no roleback.

POST - Copy Sheet (Tab) Data To Another Spreadsheet

POST http://sheets2api.com/api/v1/sheets/copy

In order for this api call to succeed you need to specify the following parameters:

  • sheet: To select the Sheet from which to copy data from.
  • copyTo: Target spreadsheet to copy data to.
  • apiId: Your api id.

Example:

{
    "sheet":  "Sheet1",
    "copyTo": "TARGET_SPREADSHEET_ID",
    "apiId": "YOUR_API_ID"
}

Response:

{
    "hidden": null,
    "index": 3,
    "rightToLeft": null,
    "sheetId": 1146724301,
    "sheetType": "GRID",
    "title": "Copy of Users",
    "gridProperties": {
        "columnCount": 26,
        "columnGroupControlAfter": null,
        "frozenColumnCount": null,
        "frozenRowCount": null,
        "hideGridlines": null,
        "rowCount": 1007,
        "rowGroupControlAfter": null
    }
}

Tip: You can test this api call using our simple API Explorer Tool here.

POST - IMPORT HTML TABLE

POST http://sheets2api.com/api/v1/sheets/importTable

Import HTML Table into your selected spreadsheet Sheet.

Request Parameters:

  • sheet: Your selected sheet to import to.
  • url: Page url to import from.
  • index: The index of the HTML Table to import, for EX: if it's the first table in the page then index=1, if the second then index=2, and so on...
  • apiId: Your api id.

Request Body:

{
    "sheet": "YOUR_SHEET",
    "url": "PAGE_URL_TO_IMPORT_FROM",
    "index": 1, // Here we are importing first table in the page
    "apiId": "YOUR_API_ID"
}

Response:

{
    "spreadsheetId": "YOUR_SPREADSHEET_ID",
    "sheetTitle": "YOUR_SHEET",
    "range": "AFFECTED_RANGE"
}

POST - IMPORT HTML LIST

POST http://sheets2api.com/api/v1/sheets/importList

Import HTML List into your selected spreadsheet Sheet.

Request Parameters:

  • sheet: Your selected sheet to import to.
  • url: Page url to import from.
  • index: The index of the HTML List to import, for EX: if it's the first list in the page then index=1, if the second then index=2, and so on...
  • apiId: Your api id.

Request Body:

{
    "sheet": "YOUR_SHEET",
    "url": "PAGE_URL_TO_IMPORT_FROM",
    "index": 1, // Here we are importing first list in the page
    "apiId": "YOUR_API_ID"
}

Response:

{
    "spreadsheetId": "YOUR_SPREADSHEET_ID",
    "sheetTitle": "YOUR_SHEET",
    "range": "AFFECTED_RANGE"
}

Authentication

You can add HTTP Basic Auth from your Apis Dashboard to secure your api if you feel there is usage abuse, However you can enable or disable this feature anytime you need.

Cashing

This feature allows your api call to get data 10x times faster than usual requests.

You can always enable this feature in your Apis Dashboard.

Notice: Cash files lasts only 1 minute, after they're deleted, they'll be created again for 1 minute.

Data Safety

Your personal data is very important to us, that means we take a good care of your data and not sharing it with any other resource.

We certainly don't use your data publicly in our humble company, so be sure to not accidentally provide your data to any other member, it could be used in the wrong way, and we are not responsible of any harm others may use against you.