Basic "New Row" Code for Google Apps Script bound to a particular type of Sheet

timeTriggered.js

//#region ---- ABOUT ----
/**
  * This Code is meant to be published as a Google Apps Script WebApp.
  * 
  * # Pre-work 
  * ## Setting Trigger to add a new row each day.
  * 1. Set a "Trigger" in Apps Script to run the `oneAM()" function each night between midnight and 1am. This makes a new row for you & copies formulas to it.
  * 
  * ## Making the web app
  * 1. Click Publish > Deploy as web app...
  * 2. CHANGE "Who has access to the app:" TO "Everyone, even anonymous. 
      > [!WARNING] This is means anyone with your webapp address could write files to your folder! Take care that you do not share the WebApp URL, anything that's logging your webtraffic could pick it up.
  * 3. Click "Okay" or "Publish" or "Update" or whatever it says.
  * 4. Copy the URL of the web app for use in Siri Shortcuts (or wherever)
  * 
  * ## Using the web app
  * - Use any means to send GET requests to the URL, literally just pasting to your URL in a browser will work.
  * - Use Siri Shortcuts (or any other method) to send POST Messages to add new data.
  *   - POST body parameters:
  *     - "sheet": "..." // where "sheet" is the key and the value is the name of the sheet to be updated
  *     - "columns": ["...","..."] // where "columns" is the key and the value is an array of values to be placed in columns, starting from the column to the right of the "Timestamp" column
  *   - Siri Shortcuts tips: 
  *     - Use the "Get Contents of URL" action
  *     - Add a Header with key `Content-Type` and value `application/json`
  *     - For Request Body, either do "JSON" and fill it in directly in this action, or do "File" and pass in a Dictionary 
  *
  * Assumptions made in code:
  * - Period sheets names are correct in the `globalVars.gs` script
  * - Other data-holding sheets follow the common schema with "A:E" as formulas referencing a Timestamp stored in column "F". An arbitrary number of columns can come after that for holding data.
  * - Sheets are **sorted** - most recently-dated row on top, oldest-dated row on bottom
  */
//#endregion


//#section ---- Global Vars ----

//sheet list
const workbook = SpreadsheetApp.getActiveSpreadsheet();
const daySheet = workbook.getSheetByName("Days");
const weekSheet = workbook.getSheetByName("Weeks");
const monthSheet = workbook.getSheetByName("Months");
const quarterSheet = workbook.getSheetByName("Quarters");
const yearSheet = workbook.getSheetByName("Years");

// constants
const FIRST_DATE_STR = '4/22/2013'; // first day of tracking, must be parsable using `new Date(firstDateAsString)`

const TODAY_STR = new Date().toDateString();

//#endsection

//#section ---- Time Triggered ----

/////////////////////////////////////////////////////
//This is the function that is triggered each night//
/////////////////////////////////////////////////////
function oneAM(){  
  //make room for today
  newDay();

  let dataDate = new Date();

  //check if room needs to be made in the week/month/year sheets
  if(dataDate.getDay() == 1){ //new week condition
    newRow(weekSheet,'D');
  }else{
    daySheet.getRange("2:2").setBorder(false,null,false,null,null,null); //if not a new week
  }
  if(dataDate.getDate() == 1){ //new month condition
    newRow(monthSheet,'E');
    if(dataDate.getMonth() % 4 == 0){ //new quarter condition
      newRow(quarterSheet,'F');
    }
    if(dataDate.getMonth() == 0){ //new year condition
      newRow(yearSheet,'G');
    }
  }
}

function newDay(dateIn){  
  daySheet.insertRowBefore(2);
  //copy all the relevant formulas from the row below
  daySheet.getRange("3:3").copyTo(daySheet.getRange("2:2"));
  daySheet.getRange("A2").setValue(TODAY_STR); // overwrite the date
  daySheet.getRange("M2:U2").clearContent(); // retain formulas, delete raw data
}

function newRow(sht,colLetter){
  sht.insertRowBefore(2);
  sht.getRange("3:3").copyTo(weekSheet.getRange("2:2"));
  sht.getRange('A2').setValue(daySheet.getRange(colLetter + "2").getValue());
}

//#endsection

//#section ---- WebApp ----

//#region ---- WEB APP ENDPOINTS ----
/**
 * Treating doPost as literally a "POST"ing new data.
 * Timestamp will always be set to right now.
 * Post request needs 2 parameters:
 * {
 *  sheet: "Sheet_To_Be_Updated_Name",
 *  columns: ["array of values","associated with columns", "starting from the first 'data' column"]
 * }
 * If "sheet" is the string literal "Days", then the TOP ROW is written to. Else the sheet will have a row ADDED with the new contents.
 * Maybe more later is **IF NEEDED**
 */
function doPost(e) {
  Logger.log('Received POST...', e);
  const params = JSON.parse(e.postData.contents);
  if (params.sheet === undefined || params.columns === undefined) return makeErrMsg('Missing "sheet" and/or "columns" keys');

  const affectedSheet = workbook.getSheetByName(params.sheet);
  if (affectedSheet === null) return makeErrMsg('The "sheet" key did not match any named sheet.');

  let range, newValues; // declaring in this scope on purpose

  try {
    if (params.sheet === 'Days') {
      //Update the already-in-place row;
      range = daySheet.getRange('M2:U2');
      newValues = range.getValues();
      params.columns.forEach((val, i) => {
        if (val === '') return; //don't overwrite any existing data with blanks
        newValues[0][i] = val;
      })
      range.setValues(newValues);
    } else {
      //Creating new row for the data
      affectedSheet.insertRowAfter(1); //make space
      affectedSheet.getRange("A3:E3").copyTo(affectedSheet.getRange("A2:E2")); //move formulas
      //placing new data
      newValues = [[new Date(), ...params.columns]]; //prepend with Timestamp
      range = affectedSheet.getRange(2, 6, 1, newValues[0].length); //find target range (columns), starting from "Timestamp" column (6)
      range.setValues(newValues); //put the new data in place
    }
  } catch (err) {
    return makeErrMsg(err);
  }

  const responseMsg = {
    success: true,
    sheet: params.sheet,
    newContents: newValues[0]
  }
  return ContentService.createTextOutput(JSON.stringify(responseMsg, null, 2));
}

function makeErrMsg(msg) {
  const content = {
    success: false,
    err: msg
  }
  return ContentService.createTextOutput(JSON.stringify(content, null, 2));
}

/**
* Treating doGet() as literally a *getter*. Only supporting "get today" up front.
* Maybe more later is **IF NEEDED**
*/
function doGet(e) {
  Logger.log('Received GET...');
  if (e) Logger.log("Params:", e.parameters);

  const returnObj = {
    date: TODAY_STR,
  };

  /* Load today sheet */
  //Grab data from FIRST ROW as 2D array (assumes sorting!)
  let dataArray = daySheet.getRange("M1:U2").getValues(); //WOULD NEED UPDATING IF YOU MOVE, ADD, OR DELETE COLUMNS FROM "Days"
  //append to returnObj as key/value pairs
  dataArray[0].forEach((val, ind) => {
    returnObj[val] = dataArray[1][ind];
  });

  /* Load type-dedicated sheets */
  workbook.getSheets().forEach(sht => {
    //Looking at TOP 50 ROWS ONLY, add to array any rows that match the date
    dataArray = sht.getRange('1:51').getValues();
    const headers = dataArray.shift();

    // Ignore all sheets not matching the schema for individual data sheets
    if (headers[0] !== 'Date' || headers[5] !== 'Timestamp') return;

    //Filter to rows matching TODAY
    dataArray = dataArray.filter(arr => arr[0].toDateString() === TODAY_STR);

    //add as array of objects to returnObj, using sheet name as key
    returnObj[sht.getName()] = dataArray.map(row => {
      const element = {};
      let colNum = 6; //ignoring Date, Week, Month, Quarter, Year, & Timestamp
      while (colNum < row.length) {
        element[headers[colNum]] = row[colNum]
        colNum += 1;
      }
      return element;
    })
  })

  Logger.log('...returned:', returnObj);

  return ContentService.createTextOutput(JSON.stringify(returnObj, null, 2));
}
//#endregion

//#endsection