2022.07.23 Saturday

使用Google Sheet作為資料庫

介紹

實作

1. [純讀取] Spreadsheets Data API

2. [可讀寫] Google Apps Script

範例程式 
// Sheet 網址裡 [sheet-id] 的部份
// https://docs.google.com/spreadsheets/d/[sheet-id]/
// 
//            col1 col2
// row1 ---->  |    |
// row2 ---->  |    |
//             v    v

const SpreadSheetID = "";
const SpreadSheet = SpreadsheetApp.openById(SpreadSheetID);

// simpilify output function
function textOutput(obj, mimeType = "JSON") {
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(
    ContentService.MimeType[mimeType]
  );
}

function getSheet(sheetName) {
  return SpreadSheet.getSheetByName(sheetName);
}

// ----------
// doGet
// ----------

function getAllSheetData(sheetName) {
  const Sheet = getSheet(sheetName);

  // getSheetValues(startRow, startColumn, numRows, numColumns)
  //
  // the first row is category,
  // so row starts from 2
  //
  // and if the first row is not needed,
  // the result length would be full length -1
  //
  return Sheet.getSheetValues(
    2,
    1,
    Sheet.getLastRow() - 1,
    Sheet.getLastColumn()
  );
}

function getSheetDataByRange(sheetName, range) {
  const Sheet = getSheet(sheetName);

  // wouldn't return category row (the first row) by default
  const startRow = range.startRow ? range.startRow : 2;
  const startCol = range.startCol ? range.startCol : 1;

  let rowRange =
    range.endRow && range.startRow
      ? range.endRow - range.startRow + 1
      : Sheet.getLastRow() - 1;
  let colRange =
    range.endCol && range.startCol
      ? range.endCol - range.startCol + 1
      : Sheet.getLastColumn();
  if (rowRange > Sheet.getLastRow()) rowRange = Sheet.getLastRow() - 1;
  if (colRange > Sheet.getLastColumn()) colRange = Sheet.getLastColumn();

  const data = Sheet.getSheetValues(startRow, startCol, rowRange, colRange);
  return data;
}

// columnsArray [2, 5]
// would return
//   {
//     2: [ ['col2_cell1'], ['col2_cell2'], ['col2_cell3'] ],
//     5: [ ['col5_cell1'], ['col5_cell2'], ['col5_cell3'] ]
//   }
function getSheetDataBySpecificColumns(sheetName, columnsArray) {
  const Sheet = getSheet(sheetName);
  const data = {};
  columnsArray.forEach((column) => {
    // skip the first row
    data[column] = Sheet.getSheetValues(2, column, Sheet.getLastRow(), 1);
  });
  return data;
}

function doGet(e) {
  // accept object as parameter
  const params = e?.parameter;
  if (!params) {
    return textOutput({ response: "200" });
  }

  // you can customize what value to use for different execution
  // and the object key name
  // like params.exec or case "getAllData"
  switch (params.action) {
    case "getAll":
      return textOutput(getAllSheetData("Sheet1"));
      break;
    case "getSpecificColumns":
      // accept array-like input and parse it
      return textOutput(
        getSheetDataBySpecificColumns("Sheet1", JSON.parse(params.columnsArray))
      );
      break;
    default:
      return textOutput({ response: "200" });
      break;
  }
}

// ----------
// doPost
// ----------

function appendSheetRow(sheetName, newRowData) {
  const Sheet = getSheet(sheetName);
  const newRow = Sheet.getLastRow() + 1;
  // accept numeric key as row-col index
  // for example,
  // data: { 1: "Cell 1 data", 2: "Cell 2 data", 3: "Cell 3 data" }
  // would save to the row by the key
  for (const [index, cellData] of Object.entries(newRowData)) {
    Sheet.getRange(newRow, index).setValue(cellData);
  }
}

function editSheetSpecificCell(sheetName, row, col, value) {
  getSheet(sheetName).getRange(row, col).setValue(value);
}

function deleteSheetRow(sheetName, row) {
  getSheet(sheetName).deleteRow(row);
}

function doPost(e) {
  // accept object as post data
  const postContents = JSON.parse(e?.postData.contents);
  if (!postContents) {
    return textOutput({ response: "200" });
  }

  // you can customize the key and value for different execution
  switch (postContents.action) {
    case "appendRow":
      // again, you can also customize the key and value for post data
      appendSheetRow("Sheet1", postContents.data);
      return textOutput({ response: "200" });
      break;
    case "editCell":
      editSheetSpecificCell(
        "Sheet1",
        postContents.row,
        postContents.col,
        postContents.data
      );
      return textOutput({ response: "200" });
      break;
    case "deleteRow":
      deleteSheetRow("Sheet1", postContents.row);
      return textOutput({ response: "200" });
    default:
      return textOutput({ response: "200" });
      break;
  }
}

// ----------
// onEdit
// ----------
// google apps script - onEdit trigger for specific sheets in a spreadsheet? - Stack Overflow
// https://stackoverflow.com/questions/49465608/onedit-trigger-for-specific-sheets-in-a-spreadsheet

function onEdit(e) {
  if (!e) {
    return;
  }

  const editedSheetName = e.range.getSheet().getName();
  if (editedSheetName != "SomeSheetName") {
    // some execution here
  }
}

參考文章

GAS

撰寫 Discord Webhook

function sendToDiscord(message) {
  const url      = ''; // 上方建立的 Webhook 網址

  const token    = ''; // Webhook 網址後半的 token 部份
                       // * 未確認是否已不需要
  const channel  = '#general';
  const text     = message;
  const username = 'bot';
  const parse    = 'full';

  const method   = 'POST';

  const payload = {
    'token'     : token,
    'channel'   : channel,
    'content'   : text,
    'username'  : username,
    'parse'     : parse,
  };

  const params = {
    'method': method,
    'payload': payload,
    'muteHttpExceptions': true
  };

  response = UrlFetchApp.fetch(url, params);
}

Unit Test

function assert(cond) {
  if (cond !== true) throw 'NG'
}

function assertEquals(actual, expected) {
  if (actual !== expected) throw `NG: ${actual} !== ${expected}`
}

function assertThrows(func) {
  try {
    func();
  } catch (e) {
    Logger.log(e);
    return ;
  }
  throw 'NG: No expection.';
}

Save as excel file

var sheetID = "";
var fetchUrl =
  "https://docs.google.com/feeds/download/spreadsheets/Export?key=" +
  sheetID +
  "&exportFormat=xlsx";

//OAuth
var fetchOpt = {
  headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
  muteHttpExceptions: true,
};

var xlsxFile = UrlFetchApp.fetch(fetchUrl, fetchOpt)
  .getBlob()
  .setName("filename.xlsx");

參考文章

Google Charts

[番外？] 客製化 Google Form

參考文章