I currently have a script that pushes data out to a webhook (to Integromat), whenever a cell is edited.

The end-goal is to get a JSON with key:value pairs, but so far I only managed to get my values out. But I can't properly re-use those inside Integromat. So I need to get the keys (column titles in row 1) mapped to it as well. But I'm stuck :')

This is my current code:

    function onEdit(e){
  const endColumn = 20;    //<--- number of columns
  const sheet = SpreadsheetApp.getActiveSheet();

  var range = e.range; 
  var row = range.rowStart;
  var range = sheet.getRange(row, 1, 1, endColumn);
  var values = range.getValues()[0];
  Logger.log(values)

  var formData = {
      'CELL_UPDATED': range.getA1Notation(),
      'NEW_VALUE': range.getValue(),
      'SHEET': sheet.getName(),
      'CONTENT': JSON.stringify(values)
  };

  //console.log(formData);

  var options = {
    'method' : 'post',
    'payload' : formData
  };
 UrlFetchApp.fetch('webhookurl', options);
}

What would the most efficient option be to add column headers to my JSON output?

CodePudding user response：

Try this:

function onEdit(e) {
  const endColumn = 20;
  const sh = e.range.getSheet();
  let formData = { 'CELL_UPDATED': e.range.getA1Notation(), 'NEW_VALUE': e.value, 'SHEET': sh.getName(), "CONTENT": [] };
  const hA = sh.getRange(1, 1, 1, 20).getDisplayValues().flat();
  const vs = sh.getRange(e.range.rowStart, 1, 1, 20).getValues().flat();
  hA.forEach((h, i) => { formData.CONTENT.push({h:vs[i]})});
  var options = {
    'method': 'post',
    'payload': JSON.stringify(formData);
  };
  UrlFetchApp.fetch('webhookurl', options);
}

CodePudding user response：

You have to dynamically add the headers as keys to each value.

You can first declare an empty object (content) and, then, through a forEach, create each pair of key-value pairs you want:

function onEdit(e) {
  const endColumn = 20;
  const range = e.range;
  const sheet = range.getSheet();
  const row = range.rowStart;
  const headers = sheet.getRange(1, 1, 1, endColumn).getValues()[0];
  const values = sheet.getRange(row, 1, 1, endColumn).getValues()[0];
  let content = {};
  values.forEach((value,i) => content[headers[i]] = value);
  let formData = { 
    'CELL_UPDATED': range.getA1Notation(), 
    'NEW_VALUE': e.value, 
    'SHEET': sheet.getName(), 
    'CONTENT': content 
  };
  var options = {
    'method': 'post',
    'payload': JSON.stringify(formData)
  };
  UrlFetchApp.fetch('webhookurl', options);
}

Note:

If you simply add the variable name as a key, it will not interpret it as a variable. For example, in { myKey: myValue }, the key will correspond to the string "myKey", not to whatever value was previously stored to variable myKey.

Related:

• JavaScript set object key by variable