Simplifying Data Management with Google Sheets CRUD API

Introduction: Managing data in Google Sheets can be a cumbersome task, especially when dealing with large amounts of information. To make this process smoother and more efficient, we’ve created a CRUD (Create, Read, Update, Delete) operation API for Google Sheets. In this blog post, we’ll walk you through how to use this API to perform various data operations effortlessly.

Getting Started: Our API has a straightforward endpoint: https://meruprastaar.com/api/sheets To begin using it, follow these simple steps:

Fetching Data from an Active Sheet:

• To retrieve data from the active sheet of your Google Sheet, pass the Public Google Sheet URL as a query parameter like this:
• https://meruprastaar.com/api/sheets?openByUrl=sheet_url
• By default, it retrieves up to 50 columns and up to 200 rows from the sheet.
• The API will respond with a JSON object in the following format:

{
  "status_code": 200,
  "info": "Fetched (3 Rows 2 columns) of (3 Rows 2 columns) From Sheet 'student_data'",
  "roco": [3, 2],
  "total_roco": 3, 2],
  "sheet_name": "student_data",
  "rows": [["roll_no", "name"],[1,"rohit"],[2,"Ashvin"]]
}

• The status_code indicates the success of the operation, and you’ll receive the data along with other information about the sheet.

CRUD Operations: Our API supports all four CRUD operations, allowing you to create, read, update, and delete data in your Google Sheets.

Creating Data

You have to share the sheet (Anyone with the link) to update, delete, and write new data.

To append New Data to the bottom of the current data region in a Google Sheet, send a POST request to https://meruprastaar.com/api/sheets with the desired data in the request body. The API will insert the data into your sheet.

Example using Python

Now, you can create a Python script to send a POST request to API endpoint, passing the openByUrl, rows, and use_sheet (optional) parameters as needed.

Here’s a sample Python script:

import requests
import json

# Define the URL of API web app endpoint
url = 'https://meruprastaar.com/api/sheets'

# Define the data to be appended to the Google Sheet
data_to_append = [['roll-no','name'],[1, 'rohit'], [2, 'Ashvin']]

# If you want to specify the sheet, include 'use_sheet' in the request body
request_body = {
    'openByUrl': 'https://your-google-sheet-url-here',
    'rows': data_to_append,
    'use_sheet': 'student_data'  # Replace with the actual sheet name
}
headers = {"Content-Type": "application/json"}
# Send a POST request to api endpoint
response = requests.post(url, headers=headers,data=json.dumps(request_body))

# Check the response
if response.status_code == 200:
    print(response.text)
else:
    print("Failed  ", response.status_code)
    print("Response content:" +" \n"+response.text)

---

Updating Data:

Update a Range in Google Sheet (POST Request)

Description: Use this endpoint https://meruprastaar.com/api/sheets to update a specific range in a Google Sheet. You can either edit an existing range or append data to the bottom of the sheet.

Request Method: POST

Request Body: JSON Data

Request Body Structure:

1. "openByUrl" (string, required): The URL of the Google Sheet where you want to perform the update.
2. "rows" (array of arrays, required): A list of rows and columns to update. Each inner array represents a row with its data. For example, [[1, 'some text'], [2, 'text']].
3. “use_sheet” (string, optional) The Name of Sheet
4. "edit" (boolean, optional): If set to true, it will use the "edit_range" parameter to update a specific range. If false, it will append the data to the bottom of the sheet. Defaults to false if not provided.
5. "edit_range" (array, required if "edit" is true): An array containing the starting row number, starting column number, number of rows, and number of columns. This defines the range where the "rows" data will be updated.

Example Request Body:

{
    "openByUrl": "https://docs.google.com/spreadsheets/d/your_spreadsheet_id/edit",
    "rows": [
        [1, "Updated Data 1"],
        [2, "Updated Data 2"]
    ],
    "edit": True,
    "edit_range": [3, 1, 2, 2]
}

Response:

• If the update is successful, the API will return a success response with an appropriate status code.
• If there’s an error, the API will return an error response with an error message and a corresponding status code.

Example Usage:

Appending Data to the Sheet:

To append data to the bottom of the sheet, set "edit" to false. The API will automatically add the data to the current data region in the sheet.

Notes:

• Make sure the Google Sheet URL is valid and accessible.
• The "edit_range" parameter is required if "edit" is set to true. It defines the range where the update will occur.

---

---

Reading Data:

We’ve already covered how to read data from both the active sheet and specific sheets.

Get Sheet Information:

Endpoint: https://meruprastaar.com/api/sheets

Description: Use this endpoint to retrieve a specific range of data from a Google Sheet.

Request Method: GET

Query Parameters:

1. "openByUrl" (string, required): The URL of the Google Sheet from which you want to retrieve data.
2. "getSheetInfo" (string, optional): A query parameter specifying the Sheet Name

Sample Response

{
  "status_code": 200,
  "info": "Information has been successfully fetched for the specified sheet.",
  "sheetName": "Sheet1",
  "rowCount": 201,
  "columnCount": 4,
  "createdDate": "2023-08-24T12:28:15.211Z",
  "lastModifiedDate": "2023-09-23T13:30:21.238Z",
  "author": "[email protected]",
  "permissions": {
    "editors": [
      "[email protected]",
      "[email protected]"
    ],
    "viewers": [
      "[email protected]",
      "[email protected]"
    ]
  }
}

---

Get a Specific Range from Google Sheet (GET Request)

Endpoint: https://meruprastaar.com/api/sheets

Description: Use this endpoint to retrieve a specific range of data from a Google Sheet.

Request Method: GET

Query Parameters:

1. "openByUrl" (string, required): The URL of the Google Sheet from which you want to retrieve data.
2. "get_range" (string, optional): A query parameter specifying the range you want to fetch. It should be in the format starting_row_number,starting_column_number,how_many_rows,how_many_columns. For example, "1,1,5,3" would retrieve a range starting from row 1, column 1, with 5 rows and 3 columns.

Example Request:

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&get_range=1,1,5,3

Response:

The API will return the data within the specified range in the Google Sheet.

• If the request is successful, you will receive the data in the response body with an appropriate status code.
• If there’s an error or the specified range is invalid, the API will return an error response with an error message and a corresponding status code.

---

Deleting Row/Column :

To remove Row/Column , send a GET request to https://meruprastaar.com/api/sheets with the row and column number to delete. This will Remove the specified Row/Column.

Example

pass the Google Sheet URL and Row and Column Number as a query parameter like this:

https://meruprastaar.com/api/sheets?openByUrl=sheet_url&del_roco=1,4

roco is short name for row and column

import requests

# Define the URL of API web app endpoint
api_url= 'https://API-url-here'

# Make an HTTP GET request to the web app URL
sheet_url='url_of_google_sheet'

row_to_delete=1
column_to_delete=4

#Passing row number zero will remove only the column.
#Passing column number zero will remove only the row.

response = requests.get(api_url+f'?openByUrl={sheet_url}&del_roco={row_to_delete},{column_to_delete}')

# Check the response

if response.status_code == 200:
    print(response.text)
else:
    print("Failed to Delete row/column. Status code:", response.status_code)
    print("Response content:" +" \n"+response.text)

---

Insert Blank Rows/Columns in Google Sheet (GET Request)

Endpoint: https://meruprastaar.com/api/sheets

Description: Use this endpoint to insert blank rows or columns in a Google Sheet.

Request Method: GET

Query Parameters:

1. "openByUrl" (string, required): The URL of the Google Sheet where you want to insert rows or columns.
2. "insert_roco" (string, optional): A query parameter specifying the insertion operation. It should be in the format count,type,position,at.
   • "count" (integer, required): The number of rows or columns to insert.
   • "type" (string, required): Type of insertion, use “r” for rows or “c” for columns.
   • "position" (string, required): Position of insertion, use “b” for before or “a” for after.
   • "at" (integer, required): The row or column number at which to perform the insertion.

Example Requests:

Insert 1 Row Before the 10th Row:

To insert 1 row before the 10th row in the Google Sheet:

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&insert_roco=1,r,b,10

Insert 1 Column Before the 10th Column:

To insert 1 column before the 10th column in the Google Sheet:

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&insert_roco=1,c,b,10

Response:

• If the request is successful, the API will return a success response with an appropriate status code.
• If there are any errors or the insertion operation fails, the API will return an error response with an error message and a corresponding status code.

Example Usage:

Use the provided query parameters to insert blank rows or columns in the Google Sheet. Construct the appropriate GET request for the desired operation.

Notes:

• Ensure that you have access to a public Google Sheet (as an editor or viewer).
• The API will respond with the results of the requested insertion operation.

---

Managing Sheets in Google Spreadsheet (GET Requests)

Endpoint: https://meruprastaar.com/api/sheets

Description: Use this endpoint to manage sheets within a Google Spreadsheet. You can retrieve a list of sheets, rename sheets, delete sheets, and insert new sheets.

Request Method: GET

Query Parameters:

1. "openByUrl" (string, required): The URL of the Google Spreadsheet you want to perform sheet operations on.
2. "get_sheets" (boolean, optional): To get a list of sheets, set this parameter to true.
3. "rename_sheet" (string, optional): To rename a sheet, set this parameter in the format which_sheet_to_rename|new_name_of_sheet.
4. "delete_sheet" (string, optional): To delete a sheet, specify the name of the sheet you want to delete.
5. "insert_sheet" (string, optional): To insert a new sheet, provide the name of the sheet you want to insert.

Example Requests:

a. Get List of Sheets:

To get a list of sheets in the Google Spreadsheet:

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&get_sheets=true

b. Rename a Sheet:

To rename a sheet, provide the old sheet name and the new name:

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&rename_sheet=old_sheet_name|new_sheet_name

c. Delete a Sheet:

To delete a sheet, specify the name of the sheet you want to delete:

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&delete_sheet=sheet_name_to_delete

d. Insert a New Sheet:

To insert a new sheet, provide the name of the sheet you want to insert:

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&insert_sheet=new_sheet_name_to_insert

Response:

• If the requests are successful, the API will return a success response with an appropriate status code.
• If there are any errors or the requested sheet operations fail, the API will return an error response with an error message and a corresponding status code.

Example Usage:

Use the provided query parameters to interact with sheets within the Google Spreadsheet. Construct the appropriate GET request for the desired operation.

Notes:

• Verify that the sheet names provided in the query parameters exist in the spreadsheet.
• The API will respond with the results of the requested sheet operations.

---

Fetch Sheet Data with Query (GET Request)

Endpoint: https://meruprastaar.com/api/sheets

Description: Use this endpoint to fetch data from a Google Sheet by writing a query. You can specify the query, format, range, and sheet to retrieve data as needed.

Request Method: GET

You have to share the sheet (Anyone with the link) to User query feature.

Query Parameters:

1. "openByUrl" (string, required): The URL of the Google Sheet from which you want to retrieve data.
2. "query" (string, optional): The query string you want to execute to fetch specific data from the sheet.
3. "format" (string, optional): The desired format for the retrieved data. Supported formats are JSON, CSV, and HTML. By default, it returns data in CSV format.
4. "range" (string, optional): The range in A1 notation to specify the data range you want to query. If not provided, the query may run on the entire sheet.
5. "use_sheet" (string, optional): If you want to fetch data from a specific sheet within the workbook, specify the sheet name or identifier.

Example Request:

To fetch data from a Google Sheet using a query, specify the "openByUrl" parameter for the sheet URL and the "query" parameter for your SQL-like query. Additionally, you can specify "format" to request a specific format, "range" to query within a particular range, and "use_sheet" to target a specific sheet.

GET https://meruprastaar.com/api/sheets?openByUrl=sheet_url&query=SELECT A,B limit 10&format=html&range=A1:B10&use_sheet=Sheet1

---

Our Google Sheets CRUD API simplifies data management, making it easy to interact with your spreadsheets programmatically. Whether you need to retrieve data, add new records, update existing entries, or delete obsolete information, our API streamlines these operations. It’s a powerful tool for developers and businesses looking to enhance their data handling capabilities in Google Sheets. Start using our API today and take your data management to the next level!