Google Apps Script General API

general-api.gs

let book = SpreadsheetApp.openByUrl('');
let sheet = book.getSheetByName("工作表1");

function doGet(e) {  
  let data = sheet.getDataRange().getValues()
  let headers = data[0];
  
  let arr = [];

  for (let i = 1; i < data.length; i++) {
    let row = data[i];
    let obj = {};

    for (let j = 0; j < row.length; j++) {
      obj[headers[j]] = row[j];
    }

    arr.push(obj);
  }

  let res = JSON.stringify(arr);

  console.log(headers)
  console.log(res)

  return ContentService.createTextOutput(res).setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  try {
    let contents = JSON.parse(e.postData.contents);
    let action = contents.action;

    console.log('POST data:', contents);

    let res;

    switch (action) {
      case 'post':
        res = appendNewRow(contents);
        break;
      case 'delete':
        res = deleteByIndex(contents);
        break;
      case 'update':
        res = updateRow(contents);
        break;
      default:
        throw new Error('Invalid action');
    }

    return res;
  } catch (error) {
    console.error(error.message);

    let res = {
      status: 'error',
      message: error.message
    }

    return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
  }
}

function appendNewRow(contents) {
  try {
    // Get the headers
    let headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

    // Check if the keys in the data are all included in the headers
    let reqData = contents.data

    let dataKeys = Object.keys(reqData);
    if (!dataKeys.every(key => headers.includes(key))) {
      throw new Error('Posted data includes headers not in the table');
    }

    let row = headers.map(header => reqData[header] || '');

    sheet.appendRow(row);

    // 特定欄位加入公式 ===================================
//     let durationColumn = 3;
//     let lastRow = sheet.getLastRow();
//     sheet.getRange(lastRow, durationColumn).setFormula('=B' + lastRow + '-A' + lastRow);
    // ==================================================

    let res = {
      status: 'success'
    }

    return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    console.error(error.message);

    let res = {
      status: 'error',
      message: error.message
    }

    return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
  }
}

function deleteByIndex(contents) {
  try {
     let index = contents.index;

     if (typeof index !== 'number' || index < 0 || index > sheet.getLastRow() - 2) {
      throw new Error('Invalid row index');
    }

    sheet.deleteRow(index + 2); // +2 because the first row is headers and rows are 1-indexed

    let res = {
      status: 'success',
      message: `Row ${index + 2} deleted successfully`
    };

    return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
  } catch(error) {
    console.error(error.message);

    let res = {
      status: 'error',
      message: error.message
    };

    return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
  }
}

function updateRow(contents) {
  try {
    let index = contents.index;
    let reqData = contents.data

    if (typeof index !== 'number' || index < 0 || index > sheet.getLastRow() - 2) {
      throw new Error('Invalid row index');
    }

    // Check if the keys in the data are all included in the headers
    let headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

    // 不能更新 "duration" ===================================
//     if (reqData.hasOwnProperty('duration')) {
//       throw new Error('Cannot update the "duration" column');
//     }
    // ======================================================
   
    let dataKeys = Object.keys(reqData);
    if (!dataKeys.every(key => headers.includes(key))) {
      throw new Error('reqData includes headers not in the table');
    }

    // Get the existing row data
    let existingRow = sheet.getRange(index + 2, 1, 1, headers.length).getValues()[0];

    // Use the existing row data as the default value
    let row = headers.map((header, i) => reqData.hasOwnProperty(header) ? reqData[header] : existingRow[i]);

    sheet.getRange(index + 2, 1, 1, row.length).setValues([row]);

    let res = {
      status: 'success',
      message: `Row ${index + 2} updated successfully`
    };

    return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
  } catch(error) {
    console.error(error.message);

    let res = {
      status: 'error',
      message: error.message
    };

    return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
  }
}