Google Apps Script Update Column S when there's a change and add new data below it-CodePudding

so I have this code that I got here in which it updates column B when it detects change and also adds new data below it.

function myFunction() {
  // 1. Retrieve values from the source and target sheets.
  var ss = SpreadsheetApp.getActive();
  var [srcSheet, targetSheet] = ['Source Sheet', 'Target Sheet'].map(s => ss.getSheetByName(s));
  var [srcValues, targetValues] = [[srcSheet, "A2:S"], [targetSheet, "A2:S"]].map(s => s[0].getLastRow() == 1 ? [] : s[0].getRange(s[1]   s[0].getLastRow()).getValues());

  // 2. Create objects for searching values of the column "A".
  var [srcObj, targetObj] = [srcValues, targetValues].map(e => e.reduce((o, [a, ...b]) => (o[a] = b, o), {}));

  // 3. Check update values at the target sheet.
  var updatedValues = targetValues.map(([a, ...b]) => [a, ...(srcObj[a] || b)]);
  // 4. Check append values.
  var appendValues = srcValues.reduce((ar, [a, ...b]) => {
    if (!targetObj[a]) ar.push([a, ...b]);
    return ar;
  }, []);

  // 5. Update the target sheet.
  var values = [...updatedValues, ...appendValues];
  targetSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

But what I want right now is to update the column S instead of Column B, though I am a bit clueless on how I'm going to do it.

CodePudding user response：

Try to look for an answer and found this one, though what it does is it updates the whole row instead of the specific column that I want to update, but I think this will work fine for now:

function Test() {
  // 1. Retrieve values from the source and target sheets.
  var ss = SpreadsheetApp.getActive();
  var [srcSheet, targetSheet] = ['Source Sheet', 'Target Sheet'].map(s => ss.getSheetByName(s));
  var [srcValues, targetValues] = [[srcSheet, "A2:S"], [targetSheet, "A2:S"]].map(s => s[0].getLastRow() == 1 ? [] : s[0].getRange(s[1]   s[0].getLastRow()).getValues());

  // 2. Create objects for searching values of the column "A".
  var [srcObj, targetObj] = [srcValues, targetValues].map(e => e.reduce((o, [a, ...b]) => (o[a] = b, o), {}));

  // 3. Check update values at the target sheet.
  var updatedValues = targetValues.map(([a, ...b]) => [a, ...(srcObj[a] || b)]);
  // 4. Check append values.
  var appendValues = srcValues.reduce((ar, [a, ...b]) => {
    if (!targetObj[a]) ar.push([a, ...b]);
    return ar;
  }, []);

  // 5. Update the target sheet.
  var values = [...updatedValues, ...appendValues];
  targetSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}