This solution turns your Google Sheets spreadsheet into a dynamic web API, allowing you (or your clients) to read, create, update, and delete data directly via HTTP requests. It's perfect for managing data for multiple clients without the need for complex servers – all running free on Google Apps Script!
We'll go from basic to advanced, with detailed steps. If you follow precisely, you'll have your API up and running in 5 minutes. You can adapt it to your needs, such as managing integrations for different clients.
With this API, you or your systems can:
-
Read all data
-
Search for specific records
-
Add new rows
-
Update existing rows
-
Delete rows
All via simple HTTP requests (GET and POST).
1. The Complete API Code (Copy and Paste)
Here is the ready script that you will paste into Google Apps Script. It has been tested and works perfectly:
/**
* Google Spreadsheet API – FINAL CORRECTED VERSION
* Exact equality search (===)
* Corrected method logic
*/
const SPREADSHEET_ID = '[SPREADSHEET_ID]';
const SHEET_NAME = '[SHEET_NAME]';
const CACHE_TTL = 10 * 60 * 1000; // 10 minutes
let cache = { data: [], headers: [], lastUpdate: 0 };
// Web App entry points
function doGet(e) { return handleRequest(e, 'GET'); }
function doPost(e) { return handleRequest(e, 'POST'); }
function handleRequest(e, httpMethod) {
let payload = {};
// Read JSON body (only in POST)
if (httpMethod === 'POST' && e.postData && e.postData.type === 'application/json') {
try {
payload = JSON.parse(e.postData.contents || '{}');
} catch (err) {
return jsonResponse({ status: 'error', message: 'Invalid JSON in body' });
}
}
// Refresh cache if needed
if (Date.now() - cache.lastUpdate > CACHE_TTL) {
refreshCache();
}
// Detect simulated method (_method in payload or parameter)
const simulatedMethod = (payload._method || e.parameter.method || '').toUpperCase();
// ==========================
// GET → List all
// ==========================
if (httpMethod === 'GET') {
return jsonResponse({
status: 'success',
total: cache.data.length,
data: cache.data
});
}
// ==========================
// POST
// ==========================
if (httpMethod === 'POST') {
// Simulate PATCH
if (simulatedMethod === 'PATCH') {
if (!payload.filter || !payload.updates) {
return jsonResponse({ status: 'error', message: 'PATCH requires "filter" and "updates"' });
}
return jsonResponse(updateRow(payload.filter.column, payload.filter.value, payload.updates));
}
// Simulate DELETE
if (simulatedMethod === 'DELETE') {
if (!payload.filter) {
return jsonResponse({ status: 'error', message: 'DELETE requires "filter"' });
}
return jsonResponse(deleteRow(payload.filter.column, payload.filter.value));
}
// Filter by exact match
if (payload.filter && payload.filter.column && payload.filter.value !== undefined) {
const col = payload.filter.column.trim();
const val = payload.filter.value.toString();
const results = cache.data.filter(row =>
String(row[col] || '') === val
);
return jsonResponse({
status: 'success',
found: results.length > 0,
total: results.length,
data: results
});
}
// Create new row (any JSON without filter or _method)
if (Object.keys(payload).length > 0) {
return jsonResponse(createRow(payload));
}
return jsonResponse({ status: 'error', message: 'Empty or invalid body for POST' });
}
return jsonResponse({ status: 'error', message: 'Unsupported HTTP method' });
}
// Refresh cache
function refreshCache() {
const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
const [header, ...rows] = sheet.getDataRange().getValues();
const headers = header.map(h => String(h).trim());
const data = rows.map(row => {
const obj = {};
headers.forEach((h, i) => obj[h] = row[i]);
return obj;
});
cache = { data, headers, lastUpdate: Date.now() };
}
// Create new row
function createRow(obj) {
const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
const headers = cache.headers;
const invalids = Object.keys(obj).filter(k => !headers.includes(k));
if (invalids.length > 0) {
return { status: 'error', message: `Invalid fields: ${invalids.join(', ')}` };
}
const newLine = headers.map(h => obj[h] !== undefined ? obj[h] : '');
sheet.appendRow(newLine);
const newObj = {};
headers.forEach((h, i) => newObj[h] = newLine[i]);
cache.data.push(newObj);
return { status: 'success', message: 'Row created', data: newObj };
}
// Update row (first match)
function updateRow(column, value, updates) {
const idx = cache.data.findIndex(row =>
String(row[column] || '') === String(value)
);
if (idx === -1) return { status: 'error', message: 'Row not found' };
const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
const headers = cache.headers;
const rowNumber = idx + 2;
Object.keys(updates).forEach(key => {
if (headers.includes(key)) {
const col = headers.indexOf(key) + 1;
sheet.getRange(rowNumber, col).setValue(updates[key]);
cache.data[idx][key] = updates[key];
}
});
return { status: 'success', message: 'Row updated', data: cache.data[idx] };
}
// Delete row
function deleteRow(column, value) {
const idx = cache.data.findIndex(row =>
String(row[column] || '') === String(value)
);
if (idx === -1) return { status: 'error', message: 'Row not found' };
const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
sheet.deleteRow(idx + 2);
const deleted = cache.data.splice(idx, 1)[0];
return { status: 'success', message: 'Row deleted', deletedData: deleted };
}
// JSON Response
function jsonResponse(obj) {
return ContentService
.createTextOutput(JSON.stringify({ data: obj }, null, 2))
.setMimeType(ContentService.MimeType.JSON);
}
2. How to Configure Your Spreadsheet Details in the Code
Before pasting the code, replace two parts:
-
[SPREADSHEET_ID] → The unique ID of your Google Sheets document.
-
Open your spreadsheet in the browser.
-
Check the URL: https://docs.google.com/spreadsheets/d/ABC123XYZ456/edit#gid=0
-
The ID is the part between /d/ and /edit: in the example, ABC123XYZ456.
-
Replace [SPREADSHEET_ID] with this value (without extra quotes).
-
-
[SHEET_NAME] → The name of the sheet (tab) you want to use.
-
At the bottom of your spreadsheet, see the sheet's name (default is "Sheet1" or "Planilha1").
-
Exactly match the sheet name (case-sensitive).
-
Never include accents or special characters.
-
Example: if the sheet is named "Clients", put 'Clients'
-
After replacing these two values, your code is ready to use!
3. Opening the Script Editor and Pasting the Code
- In your spreadsheet, click the top menu: Extensions > Apps Script.
- In the editor that opens, delete all default code (e.g., function myFunction() {}):
.png)
- Paste the entire code from section 1 (with SPREADSHEET_ID and SHEET_NAME replaced), then click Save (the disk icon) and give your project a name:
.png)
4. Publishing as a Web App (Making Your API Public)
-
In the Apps Script editor, click Deploy > New deployment.
-
Click Select type > Web app.
-
Fill out:
-
Execute as: "Me" (your account)
-
Who has access: "Anyone"
-
-
Click Deploy.
-
Authorize permissions as necessary (confirmation may be needed – click "Advanced" > "Go to project" > "Allow").
-
Copy the web app URL that appears at the end (e.g., https://script.google.com/macros/s/…/exec). This is your API!
.png)
5. How to Use the API (Practical Examples)
Now that your API is published, you can use it with any HTTP request tool, such as curl (terminal), Postman, or code in any language. Always replace YOUR_URL_HERE with the URL you copied in step 4.
Below, we explain each available method separately. For POST methods, always include the header Content-Type: application/json.
Get All Data (GET)
This method returns a complete list of all spreadsheet records as a JSON array (each object representing a row, with keys based on headers).
-
How to use: Access the URL directly in the browser or via GET, with no body or extra parameters.
-
Example with curl:
curl "YOUR_URL_HERE"
-
Required Body: None (do not send a body).
-
Expected result: JSON with all data. Example (assuming 2 rows):
{
"data": {
"status": "success",
"total": 2,
"data": [
{
"id": "1",
"name": "John Smith",
"email": "[email protected]",
"balance": 1500
},
{
"id": "2",
"name": "Maria Oliveira",
"email": "[email protected]",
"balance": 3200
}
]
}
}
Filtered Return (POST)
This method searches and returns only rows that exactly match the specified value in a column (exact filter, not partial). Useful for querying specific client data.
-
How to use: Send a POST request with body containing the filter (column and value).
-
Example with curl:
curl -X POST "YOUR_URL_HERE" \
-H "Content-Type: application/json" \
-d '{
"filter": {
"column": "id",
"value": "1"
}
}'
- Required Body (formatted):
{
"filter": {
"column": "id",
"value": "1"
}
}
- Expected result: JSON with filtered rows (may be empty if none found). Example:
{
"data": {
"status": "success",
"found": true,
"total": 1,
"data": [
{
"id": "1",
"name": "John Smith",
"email": "[email protected]",
"balance": 1500
}
]
}
}
Add a New Row (POST)
This method adds a new row to the spreadsheet with the provided values. The keys in the body must match exactly the spreadsheet headers.
-
How to use: Send a POST request with body containing the data for the new row (no filter or _method).
-
Example with curl:
curl -X POST "YOUR_URL_HERE" \
-H "Content-Type: application/json" \
-d '{
"id": "3",
"name": "Carlos Santos",
"email": "[email protected]",
"balance": 5000
}'
- Required Body (formatted):
{
"id": "3",
"name": "Carlos Santos",
"email": "[email protected]",
"balance": 5000
}
- Expected result: Confirmation of creation with added data. Example:
{
"data": {
"status": "success",
"message": "Row created",
"data": {
"id": "3",
"name": "Carlos Santos",
"email": "[email protected]",
"balance": 5000
}
}
}
Update an Existing Row (POST)
This method updates values in an existing row, using a filter to identify which row to modify (updates the first match exactly). Only fields sent in "updates" are modified.
-
How to use: Send a POST request with _method: "PATCH", filter, and fields to update.
-
Example with curl:
curl -X POST "YOUR_URL_HERE" \
-H "Content-Type: application/json" \
-d '{
"_method": "PATCH",
"filter": {
"column": "id",
"value": "3"
},
"updates": {
"name": "Carlos Santos Updated",
"balance": 6000
}
}'
- Required Body (formatted):
{
"_method": "PATCH",
"filter": {
"column": "id",
"value": "3"
},
"updates": {
"name": "Carlos Santos Updated",
"balance": 6000
}
}
- Expected result: Confirmation of update with modified row data. Example:
{
"data": {
"status": "success",
"message": "Row updated",
"data": {
"id": "3",
"name": "Carlos Santos Updated",
"email": "[email protected]",
"balance": 6000
}
}
}
Delete a Row (POST)
This method deletes an existing row, using a filter to identify which to remove (deletes the first match exactly).
-
How to use: Send a POST request with _method: "DELETE" and the filter.
-
Example with curl:
curl -X POST "YOUR_URL_HERE" \
-H "Content-Type: application/json" \
-d '{
"_method": "DELETE",
"filter": {
"column": "id",
"value": "3"
}
}'
- Required Body (formatted):
{
"_method": "DELETE",
"filter": {
"column": "id",
"value": "3"
}
}
- Expected result: Confirmation of deletion with the data of the removed row. Example:
{
"data": {
"status": "success",
"message": "Row deleted",
"deletedData": {
"id": "3",
"name": "Carlos Santos Updated",
"email": "[email protected]",
"balance": 6000
}
}
}