ixr.dhr.mybluehost.me Logo

How to Perform CRUD Operations on Google Sheets with Apps Script API: A Step-by-Step Guide

by

Google Sheets is a versatile tool that, when combined with Google Apps Script, can serve as a backend database accessible via a custom API. This allows you to perform CRUD (Create, Read, Update, Delete) operations on your sheet data from external applications. In this guide, we’ll walk you through creating a RESTful API using Google Apps Script to interact with Google Sheets data.

Table of Contents

  1. Introduction to Google Apps Script
  2. Setting Up the Google Sheet
  3. Creating the Apps Script Project
  4. Enabling the API Execution
  5. Implementing CRUD Operations
  6. Deploying the Web App
  7. Testing the API Endpoints
  8. Best Practices
  9. Conclusion
  10. Frequently Asked Questions

Introduction to Google Apps Script

Google Apps Script is a cloud-based scripting language for extending the functionality of Google Workspace products like Sheets, Docs, and Forms. It uses JavaScript syntax and allows you to create web applications, automate tasks, and interact with Google services.

Why Use Apps Script for CRUD Operations?

  • Cost-Effective: No need for external databases.
  • Ease of Use: Leverage Google Sheets as a database.
  • Accessibility: Access data from anywhere via HTTP requests.
  • Scalability: Suitable for small to medium-sized applications.

Setting Up the Google Sheet

Step 1: Create a New Google Sheet

  1. Go to Google Sheets and create a new spreadsheet.
  2. Rename the sheet to something meaningful, like “API Data”.

Step 2: Set Up Column Headers

In Row 1, add the following headers:

  • A1: ID
  • B1: Name
  • C1: Email
  • D1: Phone

These will serve as the fields for our data entries.

Creating the Apps Script Project

Step 1: Open the Script Editor

  1. In your Google Sheet, click on Extensions > Apps Script.
  2. This will open the Apps Script editor in a new tab.

Step 2: Rename the Project

Click on the default project name (e.g., “Untitled project”) and rename it to “Google Sheets CRUD API”.

Enabling the API Execution

To allow external applications to access your Apps Script functions via HTTP requests, you’ll need to deploy your script as a web app.

Implementing CRUD Operations

We’ll create a doGet and doPost function to handle GET and POST requests. We’ll use URL parameters and JSON payloads to perform CRUD operations.

Structure of the Code

//javascriptCopy codefunction doGet(e) {
  // Handle GET requests
}

function doPost(e) {
  // Handle POST requests
}

Helper Functions

We’ll create helper functions for each CRUD operation:

  • createData()
  • readData()
  • updateData()
  • deleteData()

Create (Insert Data)

Code Implementation

//javascriptCopy codefunction doPost(e) {
  var action = e.parameter.action;
  var result;
  
  switch(action) {
    case 'create':
      result = createData(e);
      break;
    // Other cases
    default:
      result = { status: 'failed', message: 'Invalid action' };
  }
  
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

function createData(e) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = JSON.parse(e.postData.contents);
    
    // Generate a unique ID
    var lastRow = sheet.getLastRow();
    var id = lastRow;
    
    // Append the data
    sheet.appendRow([id, data.name, data.email, data.phone]);
    
    return { status: 'success', id: id };
  } catch (error) {
    return { status: 'failed', message: error.toString() };
  }
}

How It Works

  • Endpoint: /exec?action=create
  • Method: POST
  • Payload: JSON object with name, email, and phone.
  • Process:
    • Parses the JSON payload.
    • Appends a new row with the data.
    • Returns a success message with the assigned ID.

Read (Retrieve Data)

Code Implementation

//javascriptCopy codefunction doGet(e) {
  var action = e.parameter.action;
  var result;
  
  switch(action) {
    case 'read':
      result = readData(e);
      break;
    // Other cases
    default:
      result = { status: 'failed', message: 'Invalid action' };
  }
  
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

function readData(e) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var id = e.parameter.id;
    var data = sheet.getDataRange().getValues();
    
    var headers = data[0];
    var result = [];
    
    for (var i = 1; i < data.length; i++) {
      var row = data[i];
      var rowData = {};
      for (var j = 0; j < headers.length; j++) {
        rowData[headers[j]] = row[j];
      }
      if (!id || rowData.ID == id) {
        result.push(rowData);
      }
    }
    
    return { status: 'success', data: result };
  } catch (error) {
    return { status: 'failed', message: error.toString() };
  }
}

How It Works

  • Endpoint: /exec?action=read&id=optional
  • Method: GET
  • Parameters:
    • id (optional): If provided, returns data for the specific ID.
  • Process:
    • Reads all data from the sheet.
    • If id is provided, filters the data.
    • Returns the data as a JSON array.

Update (Modify Data)

Code Implementation

//javascriptCopy codefunction doPost(e) {
  var action = e.parameter.action;
  var result;
  
  switch(action) {
    case 'update':
      result = updateData(e);
      break;
    // Other cases
    default:
      result = { status: 'failed', message: 'Invalid action' };
  }
  
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

function updateData(e) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = JSON.parse(e.postData.contents);
    var id = data.id;
    
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();
    var headers = values[0];
    var idColIndex = headers.indexOf('ID');
    
    var rowToUpdate;
    for (var i = 1; i < values.length; i++) {
      if (values[i][idColIndex] == id) {
        rowToUpdate = i + 1;
        break;
      }
    }
    
    if (rowToUpdate) {
      for (var key in data) {
        var colIndex = headers.indexOf(key);
        if (colIndex > -1) {
          sheet.getRange(rowToUpdate, colIndex + 1).setValue(data[key]);
        }
      }
      return { status: 'success', message: 'Data updated' };
    } else {
      return { status: 'failed', message: 'ID not found' };
    }
  } catch (error) {
    return { status: 'failed', message: error.toString() };
  }
}

How It Works

  • Endpoint: /exec?action=update
  • Method: POST
  • Payload: JSON object with id and fields to update.
  • Process:
    • Parses the JSON payload.
    • Finds the row with the matching ID.
    • Updates the specified fields.
    • Returns a success message.

Delete (Remove Data)

Code Implementation

//javascriptCopy codefunction doPost(e) {
  var action = e.parameter.action;
  var result;
  
  switch(action) {
    case 'delete':
      result = deleteData(e);
      break;
    // Other cases
    default:
      result = { status: 'failed', message: 'Invalid action' };
  }
  
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

function deleteData(e) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = JSON.parse(e.postData.contents);
    var id = data.id;
    
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();
    var idColIndex = values[0].indexOf('ID');
    
    var rowToDelete;
    for (var i = 1; i < values.length; i++) {
      if (values[i][idColIndex] == id) {
        rowToDelete = i + 1;
        break;
      }
    }
    
    if (rowToDelete) {
      sheet.deleteRow(rowToDelete);
      return { status: 'success', message: 'Data deleted' };
    } else {
      return { status: 'failed', message: 'ID not found' };
    }
  } catch (error) {
    return { status: 'failed', message: error.toString() };
  }
}

How It Works

  • Endpoint: /exec?action=delete
  • Method: POST
  • Payload: JSON object with id.
  • Process:
    • Parses the JSON payload.
    • Finds the row with the matching ID.
    • Deletes the row.
    • Returns a success message.

Deploying the Web App

Step 1: Save Your Script

Click the Save icon or press Ctrl + S (Cmd + S on Mac).

Step 2: Deploy as Web App

  1. Click on Deploy > New deployment.
  2. In the Deployment type, select Web app.
  3. Set Description (optional).
  4. Under Execute as, choose Me (your email).
  5. Under Who has access, select Anyone.
  6. Click Deploy.
  7. Authorize the script by clicking Authorize access and following the prompts.
  8. Copy the Web app URL for testing.

Note: Choosing Anyone allows public access. For secure applications, consider authentication methods.

Testing the API Endpoints

Tools You Can Use

  • Postman: A powerful HTTP client for testing APIs.
  • cURL: Command-line tool for sending HTTP requests.
  • Web Browsers: For GET requests.

Example: Testing the Create Operation

Using Postman

  1. Method: POST
  2. URL: Your_Web_App_URL?action=create
  3. Headers:
    • Content-Type: application/json
  4. Body (raw JSON):
jsonCopy code{
  "name": "Alice Johnson",
  "email": "[email protected]",
  "phone": "555-1234"
}
  1. Send Request: Click Send.
  2. Response: Should receive a JSON object with status: "success" and the assigned id.

Example: Testing the Read Operation

Using a Web Browser

  • URL: Your_Web_App_URL?action=read
  • Result: Displays all data in JSON format.

Filtering by ID

  • URL: Your_Web_App_URL?action=read&id=2
  • Result: Displays data for the entry with ID 2.

Best Practices

Secure Your API

  • Authentication: Implement API keys or OAuth for sensitive data.
  • Input Validation: Always validate and sanitize user inputs.
  • Error Handling: Provide meaningful error messages without exposing sensitive information.

Optimize Performance

  • Batch Operations: Read and write data in batches when possible.
  • Caching: Implement caching for frequently accessed data.

Maintainability

  • Comments: Add comments to explain complex code sections.
  • Modularity: Separate code into functions for readability and reuse.
  • Logging: Use Logger.log() for debugging during development.

Conclusion

By leveraging Google Apps Script, you can transform Google Sheets into a simple yet effective backend database accessible via a custom API. This enables you to perform CRUD operations on your sheet data from external applications, opening up a world of possibilities for integration and automation.

Frequently Asked Questions

Is it safe to deploy the web app with access set to “Anyone”?

Setting access to “Anyone” allows public access to your API, which may not be secure for sensitive data. Implement authentication methods like API keys or OAuth to secure your API.

How do I handle authentication in my API?

You can pass an API key as a parameter or in the header and validate it in your script. For more robust security, consider using OAuth 2.0 with Google Cloud.

Can I use this API for commercial applications?

Yes, but be mindful of Google Apps Script quotas and limitations. For high-demand applications, consider using a dedicated backend service.

What are the limitations of using Google Sheets as a database?

  • Scalability: Not suitable for large-scale applications.
  • Performance: Slower read/write times compared to traditional databases.
  • Concurrency: Limited support for simultaneous writes.

How do I update multiple rows at once?

You can modify the updateData() function to accept an array of data objects and use batch updates to modify multiple rows.

References

all_in_one_marketing_tool