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

Time:08-25

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 and I'd appreciate a little help.

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:": ["[email protected]"]
  },
  "range": {
    "columnEnd": 26, "columnStart": 1, "rowEnd": 5, "rowStart": 5
  },
  "source": {},
  "triggerUid": "678678678",
  "values": ["8/24/2022 14:13:53", "Test Name", "John Doe", "[email protected]"]
}

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

Thanks.

CodePudding user response:

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);
}
  • Related