google-apps-script

How to map object values according to the column headers on a Spreadsheet?


I have been using Yuri's answer when I have 2 multidimensional arrays to map one's values according to the other and setValues() correctly - column index wise.

Now, I have an Object like the one below, but I couldn't manage to convert the solution linked above to serve the same purpose.

const e = {
  "authMode": "FULL", "namedValues": {
    "Timestamp": ["8/24/2022 14:13:53"],
    "Name:": ["Test Name"],
    "Primary contact's name:": ["John Doe"],
    "Primary contact's email:": ["john@email.com"]
  },
  "range": {
    "columnEnd": 26, "columnStart": 1, "rowEnd": 5, "rowStart": 5
  },
  "source": {},
  "triggerUid": "678678678",
  "values": ["8/24/2022 14:13:53", "Test Name", "John Doe", "john@email.com"]
}

This is my unsuccessful shot at it:

function saveResponse() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dbSheet = ss.getSheetByName('DB');
  const dbHeaders = dbSheet.getRange("A1:1").getValues()[0];

  let indexes = [];
  for (let r = 0; r < dbHeaders.length; r++) {
    for (let a = 0; a < Object.keys(e.namedValues).length; a++) {
      if (dbHeaders[r].indexOf(Object.keys(e.namedValues)[a]) > -1) {
        indexes.push(r);
      }
    }
  }
 //The second part is completely blank (or dark) to me
}

Now, organizing the object's values so that they can be placed into the spreadsheet according to the indexes found is where I can't get to.


Solution

  • Try this:

    function saveResponse(e) {
      const ss = SpreadsheetApp.getActive();
      const sh = ss.getSheetByName('DB');
      const hs = sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
      let r = [];
      hs.forEach(h => r.push(e.namedValues[h][0]));
      sh.appendRow(r);
    }