Adding records to tables in Parenet Child structure using Appsheet API with Appscript.

[HBT]

For certain reasons, we make the staff use wet signatures during the entry and exit times. And we enter the hours of entry and exit to work daily, in the Appsheet application.
This is a routine job that needs to be done every day.

Records are in PARENT Child structure. In the Parenet section, there is the current date and a few columns. In the child structure, there are dozens of rows and columns.

In the Appsheet application, we use the method of copying the parent child records of the previous day for each day. Then we edit the copied record with quick edit. However, due to the large number of recordings, the synchronization time reaches 30-40 minutes. This creates an annoying situation.

To get rid of this annoyance, I decided to try the Appsheet api for the first time. The result is incredibly very very fast. It adds dozens of records in a few seconds..

/* 
* 1-Declaring variables globally.
*Since the variables will be used in different functions, I made the variables to be used global.
*/

function globalVariables() {

  function dayoftheWeek() {
    var date = new Date();
    return date.getDay().toLocaleString();
  }

  function nextDayDate() {
    var date = new Date();
    date.setDate(date.getDate() + 1);
    return Utilities.formatDate(date, 'GMT+4', "dd.MM.yyyy");
  }

  var variables = {
    appID: '123456-f789-101123-8ssc5-9f1b0d8be87f',
    apiID: 'V2-dddd-l9sssQc-ssss8-Xg3d1-cccc-fJO3g-cccc-z0iTr',
    xStaffSSheetID: '1R3GY8Ossss8ttsssdIo4wvIX_rF9_PssssiLskkkzdg0BJkM',
    xdayoftheWeek: dayoftheWeek(),
    xnextDayDate: nextDayDate(),
  }
  return variables;
}

/* 2- We add a record to the parent table with the Appsheet Api. 
 * We will use the Returned ID from the Parent table in the Child Table.
 * */

function addStaffEntryExitParentTable(xdate) {

  const xjonson = {
    "Action": "Add", "Properties": { "Locale": "tr-TR", "Location": "47.623098, -122.330184" }, "Rows": [{ "date": xdate, "col1": "BLA bla" }]
  }

  const url = 'https://api.appsheet.com/api/v2/apps/' + globalVariables().appID + '/tables/StaffEntryExitParentTable/Action';
  const params = {
    'method': 'POST',
    'contentType': 'application/json',
    'headers': { 'ApplicationAccessKey': globalVariables().apiID },
    'payload': JSON.stringify(xjonson),
    'muteHttpExceptions': false
  };
  // console.log(JSON.stringify(params));
  var response = UrlFetchApp.fetch(url, params);
  var json = JSON.parse(response);
  var xStaffEntryExitParentID = json.Rows[0].ID;

  /*We call the function to add records to the child table.*/

  addStaffEntryExitChildTable(xStaffEntryExitParentID, xdate)


  /* 03-We are adding records to the Child Table. 
     We will use the ID from the Parent Table in the Child table. 
     That's why we added it as the parameter of the Function.. **/

  function addStaffEntryExitChildTable(xStaffEntryExitParentID, xdate) {

    var ss = SpreadsheetApp.openById(xStaffSSheetID);
    var data = ss.getSheetByName('StaffEntryExitChildTable').getRange('A:A').getValues();

    var xAPIPayloadObject = { "Action": "Add", "Properties": { "Locale": "tr-TR", "Location": "47.623098, -122.330184" }, "Rows": [] };
    // We created a loop for the records to be added.
    for (var i = 1; i <= data.length - 1; i++) {



      if (data[i][0] != '') {
        xAPIPayloadObject.Rows.push({
          StaffID: data[i][0],
          StaffEntryExitParentID: xStaffEntryExitParentID,
          Date: xdate,
          Entrytime: "08:00:00",
          ExitTime: "18:00:00",
          Col5: "BLA bla",
          Col6: "BLA bla",
          Col7: "BLA bla",
          Col8: "BLA bla",
        })
      }
      else {
        break;
      }

    }

    console.log(JSON.stringify(xAPIPayloadObject));
    const url = 'https://api.appsheet.com/api/v2/apps/' + globalVariables().appID + '/tables/StaffEntryExitChildTable/Action';
    const params = {
      'method': 'POST',
      'contentType': 'application/json',
      'headers': { 'ApplicationAccessKey': globalVariables().apiID },
      'payload': JSON.stringify(xAPIPayloadObject),
      'muteHttpExceptions': false
    };

    var response = UrlFetchApp.fetch(url, params);
    var data = response.getContentText();
    var json = JSON.parse(data);
    console.log(json.Rows[0].ID)

  }

}

 I added it to the trigger to run every day between 22-23. Now, when we come every morning, the records are entered. The user, on the other hand, only enters the hours of entry and exit.

A method of adding records to Parent Child tables with the Appsheet Api. I hope it will be useful to someone.

/* 04 - Here is the code we will add to the time trigger. 
 * Since the codes will be the Entry Exit records of the next day, we added +1 day in global variables above the current date. 
 * */

function runCodesAt22pm() {
  var nextWeekDay = Number(globalVariables().dayoftheWeek + 1);

  if (nextWeekDay != 0) {
    addStaffEntryExitParentTable(globalVariables().xnextDayDate);
  }

}

[Joseph_Seddik]

Thank you!