0

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?

ThomasSt
  • 185
  • 1
  • 14

2 Answers2

2

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

Learn More

Cooper
  • 59,616
  • 6
  • 23
  • 54
1

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:

Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Hmm, this gets me a lot closer, thanks! But it looks like this is creating multiple JSONs. My content looks like this (inside Integromat): {"Email Id":"test4@mail.com"},{"First Name":"K"},{"Last Name":"T"},{"Matches":0},{"Last Match":""},{"Learners Won":0},{"Last Learners Won":""},{"Applications":0} How could the code be adjusted to reach a single JSON: {"Email Id":"test4@mail.com","First Name":"K","Last Name":"T","Matches":0,"Last Match":","Learners Won":0,"Last Learners Won":"","Applications":0} – ThomasSt Oct 26 '21 at 11:25
  • @ThomasSt Sorry for the misunderstanding. I updated my answer based on your comment. Let me know if that works for you. – Iamblichus Oct 26 '21 at 11:49
  • Worked like a charm! Thanks :D – ThomasSt Oct 27 '21 at 06:29