SpreadAPI

https://github.com/ziolko/spreadapi
Admin
Free REST API for Google Spreadsheet
Tokens:5,222
Snippets:20
Trust Score:8.5
License:Apache-2.0
Update:1 year ago
Show doc for...
Tokens:
Raw
### Example GET Request Body for SpreadAPI

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/README.md

Demonstrates the basic structure for a GET request, specifying the HTTP method and the target sheet within the request body due to Google Apps Script limitations.

```javascript
{
    "method": "GET",
    "sheet": "users"
}
```

--------------------------------

### Core SpreadAPI Utility Functions

Source: https://github.com/ziolko/spreadapi/blob/master/website/setup.md

A collection of minified JavaScript functions for handling HTTP requests (GET, POST, PUT, DELETE), managing spreadsheet data (reading, writing, deleting rows, mapping objects), and implementing custom authentication and access control logic within the SpreadAPI framework.

```JavaScript
));return sheet.getRange(_id,1,1,row.length).setValues([row]),data(201)}function handleDelete(sheet,_id){return sheet.getRange("$"+_id+":$"+_id).setValue(""),data(204)}function httpResponse(data){return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON)}function error(status,code,details){return{status:status,error:{code:code,details:details}}}function data(status,data,params){params=params||{};const result={status:status,data:data};for(var key in params)params.hasOwnProperty(key)&&(result[key]=params[key]);return result}function getHeaders(sheet){const headers=sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];for(var i=headers.length-1;i>=0;i--)if(!isEmpty(headers[i]))return headers.slice(0,i+1);return[]}function isTruthy(x){return!!x}function isEmpty(item){return""===item||null==item}function find(array,predicate){if(Array.isArray(array))for(var i=0;i<array.length;i++)if(predicate(array[i]))return array[i]}function mapObjectToRow(object,headers){return headers.map((function(column){return isEmpty(column)||void 0===object[column]?"":object[column]}))}function mapRowToObject(row,_id,headers){if(row.every(isEmpty))return null;const result={_id:_id};for(var i=0;i<headers.length;i++)isEmpty(headers[i])||(result[headers[i]]=row[i]);return result}var users;function User(name,key,permissions){users||(users=[]),users.push({name:name,key:key,permissions:permissions})}function getUserWithKey(key){return find(users,(function(x){return x.key===key||"object"==typeof x&&x.key.__unsafe===key}))}function isStrongKey(key){const strongKeyRegex=new RegExp("^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[ -/:-@[-`{-~])(?=.{8,})"),user=getUserWithKey(key);return!!user&&(user.key.__unsafe===key||user.key.match(strongKeyRegex))}function getPermissions(user,spreadsheet){if(Array.isArray(user.permissions))return user.permissions;if("function"==typeof user.permissions)return user.permissions;const keys=Object.keys(user.permissions);for(var i=0;i<keys.length;i++)if(keys[i].toLowerCase()===spreadsheet.toLowerCase())return user.permissions[keys[i]];return user.permissions.ALL}function hasAccess(key,spreadsheet,method){const user=getUserWithKey(key);if(!user)return!1;const permission=getPermissions(user,spreadsheet);return!!permission&&!(permission!==ALL&&permission.toString()!==method&&!find(permission,(function(x){return x===ALL}))&&!find(permission,(function(x){return x.toString()===method})))}function GET(){}function POST(){}function PUT(){}function DELETE(){}function ALL(){}function UNSAFE(key){return{__unsafe:key}}GET.toString=function(){return"GET"},POST.toString=function(){return"POST"},PUT.toString=function(){return"PUT"},DELETE.toString=function(){return"DELETE"},ALL.toString=function(){return"*"};
```

--------------------------------

### Configure Anonymous User Access for SpreadAPI Sheets

Source: https://github.com/ziolko/spreadapi/blob/master/website/setup.md

Demonstrates how to grant specific, limited access to an anonymous user for different sheets. This example allows POST operations on the 'transactions' sheet and GET operations on the 'summary' sheet, without granting read, delete, or modify permissions for other operations.

```javascript
User("anonymous", UNSAFE(""), { transactions: POST, summary: GET });
```

--------------------------------

### Defining an Anonymous User with UNSAFE Access Key

Source: https://github.com/ziolko/spreadapi/blob/master/website/setup.md

Demonstrates how to define an anonymous user account with write access to a specific sheet ('transactions') using an UNSAFE access key, which bypasses the strong key criteria.

```JavaScript
// Anonymous account that has write access to a sheet "transactions"
User("anonymous", UNSAFE(""), { transactions: POST });
```

--------------------------------

### Example PUT Request Body with Payload for SpreadAPI

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/README.md

Illustrates how to send a PUT request, including the HTTP method, target sheet, record ID, and the payload data, all within the request body.

```javascript
{
    "method": "PUT",
    "sheet": "users",
    "id": "2",
    "payload": {
        "firstname": "John",
        "lastname": "Smith"
    }
}
```

--------------------------------

### Example JSON Request Body for Multiple Row Deletion

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/usage.md

Provides a JSON array example demonstrating how to structure the request body for removing multiple rows in a single API call, specifying the method, target sheet, and unique ID for each row.

```json
[
    { "method": "DELETE", "sheet": "users", "id": 2 },
    { "method": "DELETE", "sheet": "users", "id": 10 },
    { "method": "DELETE", "sheet": "users", "id": 5 }
]
```

--------------------------------

### Handle SpreadAPI Email Subscription Form Submission with jQuery

Source: https://github.com/ziolko/spreadapi/blob/master/website/examples/sign-up-form-example.html

This JavaScript snippet uses jQuery to manage a newsletter sign-up form. It captures form submission, validates the email input, displays a loading indicator, and sends the email data as a POST request to a Google Apps Script endpoint (SpreadAPI). Upon successful submission, a confirmation modal is displayed, which can be dismissed by clicking an 'OK' button.

```JavaScript
// This code requires jQuery
var $form = $("#newsletter-form");
var $email = $("#email");
var $modal = $("#subscription-success-modal");
var $signUp = $("#sign-up");
var $modalOK = $("#modal-ok-button");

$form.submit(function (e) {
  e.preventDefault();

  // Don't do anything if email field is empty
  var email = $email.val();
  if (!email) {
    return;
  }

  // Mark the "signup" button as "loading"
  $signUp.addClass("is-loading");

  // make the request
  $.post({
    // Replace the URL with the one specific for your script
    url: "https://script.google.com/macros/s/AKfycbzoEi2wm45iNgmtsLf6nzMIo4hxFpZvUKKFTXxJc1jEtN4W9gRo/exec",
    data: JSON.stringify({
      method: "POST",
      sheet: "emails",
      payload: {
        email
      }
    })
  }).then(function () {
    // Remove the "loading" state from the "signup" button
    $signUp.removeClass("is-loading");

    // Show the popup saying that user has been added
    // to the subscription list
    $modal.addClass("is-active");
  });
});

// Once user clicks "OK" on the popup saying that he
// has been added to the subscription list we want to
// close the popup
$modalOK.click(function () {
  $modal.removeClass("is-active");
});
```

--------------------------------

### Define User Access with Multiple Operations and ALL Alias in SpreadAPI

Source: https://github.com/ziolko/spreadapi/blob/master/website/setup.md

Illustrates various ways to define user access permissions for SpreadAPI sheets. This includes allowing multiple operations (e.g., POST and DELETE) on a single sheet, using the 'ALL' alias for all operations on a sheet, granting read-only access to all sheets, or providing full access to all sheets.

```javascript
// Can add new entries and remove them from "transactions"
// and do all operations on "summary"
User("user1", "Passw0rd!1", {
    transactions: [POST, DELETE],
    summary: ALL
});

// Can read from all sheets, but can't modify
User("user2", "Passw0rd!2", { ALL: GET });

// Can do everything in all sheets
User("user3", "Passw0rd!3", ALL);
```

--------------------------------

### SpreadAPI Custom Authentication Mechanism

Source: https://github.com/ziolko/spreadapi/blob/master/website/setup.md

Details the custom authentication mechanism used by SpreadAPI, allowing definition of multiple users with granular access levels. Each user is authenticated via an access key, which can be strong or marked as UNSAFE for anonymous or less strict access.

```APIDOC
Authentication:
  - Custom mechanism with granular access levels.
  - Users defined directly in the script.
  - Each user has an access key for authentication.

Access Key Criteria:
  - Minimum 8 characters long.
  - At least one lower case character (a-z).
  - At least one upper case character (A-Z).
  - At least one number (0-9).
  - At least one special character (#, ^, etc.).

UNSAFE Key:
  - Use `UNSAFE(key)` to bypass strong key criteria.
  - Allows empty or non-compliant access keys (e.g., for anonymous access).

Function: User(name: string, key: string | UNSAFE, permissions: object | Array | function)
  - name: The name of the user.
  - key: The access key for the user. Can be a strong string or an UNSAFE object.
  - permissions: Defines access levels. Can be an object mapping sheet names to methods (e.g., { transactions: POST }), an array of methods, or a function.

Function: UNSAFE(key: string)
  - key: The string to be used as an unsafe key.
  - Returns an object `{ __unsafe: key }` to mark the key as unsafe.
```

--------------------------------

### Configure SpreadAPI User Authentication

Source: https://github.com/ziolko/spreadapi/blob/master/website/setup.md

This snippet demonstrates various ways to configure user authentication for SpreadAPI within Google Apps Script. It shows how to define admin, regular user, and anonymous accounts with specific read/write permissions for sheets or all sheets, using the `User` function. It also highlights the use of `UNSAFE('')` for anonymous access and the importance of strong keys.

```javascript
// Admin account that has read/write access to all sheets
User("admin", "PUT_STRONG_PASSWORD_HERE", ALL);

// User account that can add entries to the "transactions" sheet
// User("user", "Passw0rd!", { transactions: POST });

// User account that can add entries to the "transactions" sheet and read from "summary"
// User("user", "Passw0rd!", { transactions: POST, summary: GET });

// Anonymous account that has write access to a specified sheet
// User("anonymous", UNSAFE(""), { transactions: POST });

// Anonymous account that has read/write access to all sheets (NOT RECOMMENDED!)
// User("anonymous", UNSAFE(""), ALL);

// Anonymous account that has read access to all sheets (NOT RECOMMENDED!)
// User("anonymous", UNSAFE(""), GET);
```

--------------------------------

### Get Single Row from SpreadAPI

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/usage.md

This API endpoint retrieves a specific row from a Google Sheet based on its ID. It requires the sheet name and an authentication key.

```APIDOC
POST https://script.google.com/macros/s/SCRIPT_ID/exec/
Request Body:
  method: string (GET)
  sheet: string (e.g., transactions, users) - Sheet name
  id: number - ID of row to get
  key: string - Authentication key
```

--------------------------------

### Get Multiple Rows from SpreadAPI

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/usage.md

This API endpoint allows retrieving a list of rows from a specified Google Sheet. Empty rows are skipped, and each returned row includes an `_id` field corresponding to its row number in Google Sheets. Results can be ordered, limited, and paginated.

```APIDOC
POST https://script.google.com/macros/s/SCRIPT_ID/exec/
Request Body:
  method: string (GET)
  sheet: string (e.g., transactions, users) - Sheet name
  key: string - Authentication key
  order: string (ASC, DESC) - Order of results (ascending by default)
  start_id: number - ID of the first row to return (default: first row)
  limit: number - Maximum number of rows in response (default: all rows)
```

--------------------------------

### Configure SpreadAPI for Anonymous Email Submission

Source: https://github.com/ziolko/spreadapi/blob/master/website/examples/sign-up-form.md

This JavaScript line, added to the SpreadAPI script, grants anonymous users permission to make POST requests to the 'emails' sheet. This setup is essential for a public sign-up form, enabling unauthenticated users to submit their email addresses directly to the Google Sheet.

```javascript
User("anonymous", UNSAFE(""), { emails: POST });
```

--------------------------------

### SpreadAPI Core Functions for Google Sheets CRUD Operations

Source: https://github.com/ziolko/spreadapi/blob/master/website/setup.md

This Google Apps Script code provides the core functionality for SpreadAPI, enabling CRUD (Create, Read, Update, Delete) operations on Google Sheets via HTTP POST requests. It includes the `doPost` entry point, `handleRequest` for routing, and specific functions for retrieving single or multiple rows (`handleGetSingleRow`, `handleGetMultipleRows`), adding new rows (`handlePost`), updating rows (`handlePut`), and deleting rows (`handleDelete`). It also incorporates access control and error handling.

```javascript
/**
 * @OnlyCurrentDoc
 */
 
function doPost(request){try{var requestData=JSON.parse(request.postData.contents)}catch(e){return httpResponse(error(400,"invalid_post_payload",{payload:request.postData.contents,type:request.postData.type}))}return Array.isArray(requestData)?httpResponse(requestData.map(handleRequest)):httpResponse(handleRequest(requestData))}function handleRequest(params){const ss=SpreadsheetApp.getActiveSpreadsheet(),sheetName=(params.sheet||"").toLowerCase(),_id=null==params.id?null:+params.id,method=(params.method||"GET").toUpperCase(),key=params.key||"";if(!hasAccess(key,sheetName,method))return error(401,"unauthorized",{});if(!isStrongKey(key))return error(401,"weak_key",{message:"Authentication key should be at least 8 characters long and contain at least one lower case, upper case, number and special character. Update your password or mark it as UNSAFE. Refer to the documentation for details."});const sheet=ss.getSheetByName(sheetName);if(!sheet)return error(404,"sheet_not_found",{sheet:sheetName});if(null!=_id&&_id<=1)return error(400,"row_index_invalid",{_id:_id});const payload=params.payload;switch(method){case"GET":return null!=_id?handleGetSingleRow(sheet,_id):handleGetMultipleRows(sheet,params);case"POST":return handlePost(sheet,payload);case"PUT":return handlePut(sheet,_id,payload);case"DELETE":return handleDelete(sheet,_id);default:return error(404,"unknown_method",{method:method})} }function handleGetSingleRow(sheet,_id){const lastColumn=sheet.getLastColumn(),headers=getHeaders(sheet),result=mapRowToObject(sheet.getRange(_id,1,1,lastColumn).getValues()[0],_id,headers);return result?data(200,result):error(404,"row_not_found",{_id:_id})}function handleGetMultipleRows(sheet,params){const lastColumn=sheet.getLastColumn(),headers=getHeaders(sheet),lastRow=sheet.getLastRow(),total=Math.max(lastRow-2+1,0),limit=null!=params.limit?+params.limit:total,isAsc="string"!=typeof params.order||"desc"!==params.order.toLowerCase();if(isNaN(limit)||limit<0)return error(404,"invalid_limit",{limit:limit});var firstRowInPage=isAsc?2:lastRow-limit+1;if(null!=params.start_id){const start_id=+params.start_id;if(start_id<2||start_id>lastRow)return error(404,"start_id_out_of_range",{start_id:start_id});firstRowInPage=start_id-(isAsc?0:limit-1)}const lastRowInPage=Math.min(firstRowInPage+limit-1,lastRow);if((firstRowInPage=Math.max(firstRowInPage,2))>lastRowInPage)return data(200,[]);const rows=sheet.getRange(firstRowInPage,1,lastRowInPage-firstRowInPage+1,lastColumn).getValues().map((function(item,index){return mapRowToObject(item,firstRowInPage+index,headers)}));isAsc||rows.reverse();var next=isAsc?lastRowInPage+1:firstRowInPage-1;return(next<2||next>lastRow)&&(next=void 0),data(200,rows.filter(isTruthy),{next:next})}function handlePost(sheet,payload){const row=mapObjectToRow(payload,getHeaders(sheet));return sheet.appendRow(row),data(201)}function handlePut(sheet,_id,payload){if(null==_id)return error(400,"row_id_missing",{});const row=mapObjectToRow(payload,getHeaders(sheet)
```

--------------------------------

### Implement Newsletter Sign-up Form with HTML, Bulma, and jQuery

Source: https://github.com/ziolko/spreadapi/blob/master/website/examples/sign-up-form.md

This complete HTML document defines a responsive newsletter sign-up form, styled with Bulma CSS. It includes an email input field and a submit button. The embedded JavaScript, utilizing jQuery, handles form submission, prevents default behavior, validates the email field, displays a loading state, and sends the email data via a POST request to the configured SpreadAPI endpoint. A success modal is shown upon successful submission.

```html
<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      rel="stylesheet"
      href="https://cdn.jsdelivr.net/npm/bulma@1.0.0/css/bulma.min.css"
    />
    <script
      src="https://code.jquery.com/jquery-3.7.1.min.js"
      integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo="
      crossorigin="anonymous"
    ></script>
    <title>Spreadapi Example Sign-up Form</title>
  </head>
  <body>
    <form id="newsletter-form">
      <div class="columns is-multiline">
        <div class="column is-6">
          <input
            class="input is-medium is-fullwidth"
            type="email"
            id="email"
            placeholder="Enter your Email"
          />
        </div>
        <div class="column is-6">
          <button
            class="button is-medium is-primary is-fullwidth is-clear"
            id="sign-up"
          >
            Sign up
          </button>
        </div>
      </div>
    </form>

    <!-- A dialog shown after successfully signing-up -->
    <div id="subscription-success-modal" class="modal">
      <div class="modal-background"></div>
      <div class="modal-content">
        <div class="box is-clearfix">
          <div>You've been added to the SpreadAPI subscription list!</div>
          <button
            class="button is-primary is-pulled-right"
            id="modal-ok-button"
          >
            OK
          </button>
        </div>
      </div>
      <button class="modal-close is-large" aria-label="close" />
    </div>

    <script>
      // This code requires jQuery

      var $form = $("#newsletter-form");
      var $email = $("#email");

      var $modal = $("#subscription-success-modal");
      var $signUp = $("#sign-up");

      var $modalOK = $("#modal-ok-button");

      $form.submit(function (e) {
        e.preventDefault();

        // Don't do anything if email field is empty
        var email = $email.val();
        if (!email) {
          return;
        }

        // Mark the "signup" button as "loading"
        $signUp.addClass("is-loading");

        // make the request
        $.post({
          // Replace the URL with the one specific for your script
          url: "https://script.google.com/macros/s/AKfycbzoEi2wm45iNgmtsLf6nzMIo4hxFpZvUKKFTXxJc1jEtN4W9gRo/exec",
          data: JSON.stringify({
            method: "POST",
            sheet: "emails",
            payload: { email },
          }),
        }).then(function () {
          // Remove the "loading" state from the "signup" button
          $signUp.removeClass("is-loading");

          // Show the popup saying that user has been added
          // to the subscription list
          $modal.addClass("is-active");
        });
      });

      // Once user clicks "OK" on the popup saying that he
      // has been added to the subscription list we want to
      // close the popup
      $modalOK.click(function () {
        $modal.removeClass("is-active");
      });
    </script>
  </body>
</html>
```

--------------------------------

### Enable Anonymous POST Access for SpreadAPI Sheet

Source: https://github.com/ziolko/spreadapi/blob/master/website/examples/404-reporting.md

This JavaScript line, used during SpreadAPI configuration, grants anonymous users permission to perform POST operations on the 'entries' sheet. It's essential for public data submission, such as reporting 404 errors without requiring user authentication.

```javascript
User("anonymous", UNSAFE(""), { entries: POST });
```

--------------------------------

### Report 404 Errors to SpreadAPI via JavaScript

Source: https://github.com/ziolko/spreadapi/blob/master/website/examples/404-reporting.md

Embed this JavaScript snippet on your 404 error page to automatically report missing URLs. It captures the current URL, referrer, and timestamp, then sends this data via a POST request to a configured SpreadAPI endpoint, allowing you to track and analyze 404 occurrences in a Google Sheet.

```markup
<script>
var spreadApiURL = 'TODO'; // Read further to get the URL
var xmlHttp = new XMLHttpRequest();
xmlHttp.open("post", spreadApiURL);
xmlHttp.send(JSON.stringify({
    method: "POST",
    sheet: "entries",
    payload: {
      time: new Date().toISOString(),
      url: document.location.href,
      referrer: document.referrer
    }
  })
);
</script>
```

--------------------------------

### API Definition: Remove Row Endpoint and Parameters

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/usage.md

Details the HTTP POST endpoint for clearing rows and specifies the required parameters for the request body, including method, sheet name, row ID, and authentication key.

```APIDOC
Endpoint:
  Method: POST
  URL: https://script.google.com/macros/s/SCRIPT_ID/exec/

Description: Clear a single row. Notice, that the row is not physically removed so that ids of the following rows don't change. To remove multiple rows pass an array of objects.

Request Body Parameters:
  - Name: method
    Type: string
    Description: DELETE
  - Name: sheet
    Type: string
    Description: Sheet name, e.g. 'transactions' or 'users'
  - Name: id
    Type: number
    Description: ID of row to remove
  - Name: key
    Type: string
    Description: Authentication key. See page 'Setup' -> 'authentication' for details.
```

--------------------------------

### Insert Row(s) into SpreadAPI Sheet

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/usage.md

This API endpoint adds one or more rows to the end of a specified Google Sheet. Multiple rows can be added by passing an array of objects, each containing the method, sheet name, and the new row's content as a payload.

```json
[
    { "method": "POST", "sheet": "users", "payload": { "Name": "Adam" } },
    { "method": "POST", "sheet": "users", "payload": { "Name": "John" } },
    { "method": "POST", "sheet": "users", "payload": { "Name": "Alex" } }
]
```

```APIDOC
POST https://script.google.com/macros/s/SCRIPT_ID/exec/
Request Body:
  method: string (POST)
  sheet: string (e.g., transactions, users) - Sheet name
  key: string - Authentication key
  payload: object - Object with new row content
```

--------------------------------

### Update Row(s) in SpreadAPI Sheet

Source: https://github.com/ziolko/spreadapi/blob/master/website/usage/usage.md

This API endpoint updates the content of one or more existing rows in a specified Google Sheet. Multiple rows can be updated by passing an array of objects, each containing the method, sheet name, row ID, and the updated row's content as a payload.

```json
[
    { "method": "PUT", "sheet": "users", "payload": { "id": 2, "Name": "Adam" } },
    { "method": "PUT", "sheet": "users", "payload": { "id": 10, "Name": "John" } },
    { "method": "PUT", "sheet": "users", "payload": { "id": 5, "Name": "Alex" } }
]
```

```APIDOC
POST https://script.google.com/macros/s/SCRIPT_ID/exec/
Request Body:
  method: string (PUT)
  sheet: string (e.g., transactions, sheets) - Sheet name
  id: string - ID of row to update
  key: string - Authentication key
  payload: object - Object with the updated row content
```
Tokens:
Raw