SpreadsheetHelper

Usage

Install

npm install sbp-google-sheet-helper or yarn add sbp-google-sheet-helper

Initialize helper

import { SpreadsheetHelper } from "sbp-google-sheet-helper";

const helper = new SpreadsheetHelper({
  fileId: "Google sheet ID from URL",
  deployKey: "Google App Script Deploy Key",
});

Get all data from sheet

const response = await helper.sheet("your_sheet").getData();

Get data with filter

// Example 1: Get product with id = 1
const response = await helper.sheet("products").getData("id", "1");

// Example 2: Get user with name = Jack
const response = await helper.sheet("users").getData("name", "Jack");

Get data with relationship (foreign linking)

// Example 1: Get products with the user info who created it
const response = await helper.sheet("products").getData();
await response.with({
  sheet: "users", // the target sheet to link with
  foreign_key: "created_by", // 'created_by' is a column name in 'products' sheet
});

// Example 2: Get products with id = 1 with the product categories
const response = await helper.sheet("products").getData("id", "1");
await response.with({
  sheet: "categories", // the target sheet to link with
  foreign_key: "categories", // 'categories' is a column name in 'products' sheet
});

Note:

1. The foreign linking supports 'One To One Relation' and 'One To Many Relation'
2. Use a string to link 2 sheets for 'One To One Relation'
3. Use an array to link 2 sheets for 'One To Many Relation' Eg: categories: [1,2,3]

Create new row to a sheet

const response = await helper.sheet("your_sheet").createData({
  // ...
  // Your data
  // ...
});

Note:

1. The tool will be matching the column name automatically
2. If the column name doesn't match, then the data will be ignored

Update a row from a sheet

const response = await helper.sheet("your_sheet").updateData(id, {
  // ...
  // Your data
  // ...
});

Note:

1. The tool will be matching the column name automatically
2. If the column name doesn't match, then the data will be ignored

Delete a row from a sheet

const response = await helper.sheet("your_sheet").deleteData(id);

Apps Script Setup

For the spreadsheet apps script setup:

1. Create/Duplicate/edit spreadsheet
2. Press 'Share' and change the 'General access' to "Anyone with the link" and press 'Done'
3. Go to 'Extensions' > 'Apps Script'
4. Paste the code below
5. Press 'Deploy' > 'New Deploy'
6. Select type 'Web app'
7. Fill everything and remember to choose "Anyone" for "Who has access"
8. Press 'Deploy'
9. Copy the 'Deployment ID' and use with this helper tool ("Deployment ID" is "deployKey")

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);

  const colLetters = [];

  const initializeCols = () => {
    for (let x = 1; x < 100; x++) {
      colLetters.push(numberToCol(x));
    }
  };

  const numberToCol = (num) => {
    var str = "",
      q,
      r;
    while (num > 0) {
      q = (num - 1) / 26;
      r = (num - 1) % 26;
      num = Math.floor(q);
      str = String.fromCharCode(65 + r) + str;
    }
    return str;
  };

  initializeCols();

  try {
    const bodyJSON = JSON.parse(e.postData.contents);
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
      bodyJSON.sheet
    );

    if (bodyJSON.crud === "create") {
      const firstRow = sheet.getSheetValues(1, 1, 1, 100)[0];
      const response = {};
      const lastRow = sheet.getLastRow() + 1;

      for (const x of Object.entries(bodyJSON.value)) {
        const colLetter = colLetters[firstRow.indexOf(x[0])];

        sheet.getRange(`${colLetter}${lastRow}`).setValue([x[1]]);
        response[x[0]] = x[1];
      }
      sheet.getRange(`A${lastRow}`).setValue([lastRow - 1]);
      response.id = lastRow - 1;

      return ContentService.createTextOutput(
        JSON.stringify(response)
      ).setMimeType(ContentService.MimeType.JSON);
    } else if (bodyJSON.crud === "update") {
      const firstRow = sheet.getSheetValues(1, 1, 1, 100)[0];
      const lastRow = sheet.getLastRow() - 1;
      const firstColumn = sheet
        .getSheetValues(2, 1, lastRow, 1)
        .map((e) => `${e[0]}`);
      const response = {};
      const targetRow = firstColumn.indexOf(`${bodyJSON.value.id}`) + 2;

      for (const x of Object.entries(bodyJSON.value)) {
        const colLetter = colLetters[firstRow.indexOf(x[0])];

        sheet.getRange(`${colLetter}${targetRow}`).setValue([x[1]]);
      }

      const updatedData = sheet.getSheetValues(targetRow, 1, 1, 100)[0];

      for (const x of updatedData) {
        response[firstRow[updatedData.indexOf(x)]] = x;
      }
      response.id = `${bodyJSON.value.id}`;

      return ContentService.createTextOutput(
        JSON.stringify(response)
      ).setMimeType(ContentService.MimeType.JSON);
    } else if (bodyJSON.crud === "delete") {
      const lastRow = sheet.getLastRow() - 1;
      const firstColumn = sheet
        .getSheetValues(2, 1, lastRow, 1)
        .map((e) => `${e[0]}`);
      const targetRow = firstColumn.indexOf(`${bodyJSON.value.id}`) + 2;

      if (targetRow >= 2) {
        sheet.deleteRow(targetRow);
      }

      return ContentService.createTextOutput(JSON.stringify({})).setMimeType(
        ContentService.MimeType.JSON
      );
    } else {
      return ContentService.createTextOutput(
        JSON.stringify({ result: "success" })
      ).setMimeType(ContentService.MimeType.JSON);
    }
  } catch (e) {
    return ContentService.createTextOutput(
      JSON.stringify({ result: "error", error: e })
    ).setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}