I'm trying to paste from source range to target range but skipping one column in target and keeping the values in it (in the sample attached I want to keep the values in column C (OTHER column). But can't find a way to keep those values or just skip the column entirely. This is the code I'm using:

function appendSheet1ToSheet2() {
      const ss = SpreadsheetApp.getActive();
      const source = ss.getRange('Sheet1!A1:C');
      const target = ss.getRange('Sheet2!A1:D');
      appendUniquesToRange_(source, target);
    }
    
    function appendUniquesToRange_(sourceRange, targetRange) {
      const dataToAppend = sourceRange.getValues().map(row => [row[0], row[1], ,row[2]]);
      const existingData = targetRange.getValues()
      const newData = existingData
        .concat(dataToAppend) 
        .filter((row, rowIndex, array) =>
          array
            .map(tuple => tuple[0])
            .indexOf(row[0]) === rowIndex && row[0] !== ''
        );
      targetRange
        .offset(0, 0, newData.length, newData[0].length)
        .setValues(newData);
    }

I've tried defining target as Range List, but then I get other errors in the 2nd function "TypeError: targetRangeList.getValues is not a function"

  const target = ss
    .getRangeList(['Sheet2!A1:B', 'Sheet2!D1:D'])
    .getRanges()
    .map(range => range.getValues());

Can't figure out how to resolve it.

CodePudding user response：

I believe your goal is as follows.

• You want to put the values from the source sheet to the destination sheet. At this time, you want to skip column "C" of the destination sheet.

In your situation, how about the following modification?

Modified script 1:

In this modification, please modify appendUniquesToRange_ as follows.

function appendUniquesToRange_(sourceRange, targetRange) {
  const dataToAppend = sourceRange.getValues();
  const [header, ...existingData] = targetRange.getValues();
  const newData = [header, ...dataToAppend.map((r, i) => [r[0], r[1], existingData[i][2], r[2]])];
  console.log(newData)
  targetRange
    .offset(0, 0, newData.length, newData[0].length)
    .setValues(newData);
}

Modified script 2:

In this modification, please modify appendSheet1ToSheet2 as follows.

function appendSheet1ToSheet2() {
  const ss = SpreadsheetApp.getActive();
  const source = ss.getRange('Sheet1!A1:C');
  const target = ss.getSheetByName('Sheet2');
  const {ab, d} = source.getValues().reduce((o, r) => {
    const temp = r.splice(0, 2);
    o.ab.push(temp);
    o.d.push(r);
    return o;
  }, {ab: [], d: []});
  target.getRange(2, 1, ab.length, ab[0].length).setValues(ab);
  target.getRange(2, 4, d.length, d[0].length).setValues(d);
}

• In this pattern, the values are retrieved and put using getValues and setValues, and the column "C" of the destination sheet is not changed.

Modified script 3:

In this modification, please modify appendSheet1ToSheet2 as follows.

function appendSheet1ToSheet2() {
  const ss = SpreadsheetApp.getActive();
  const source = ss.getSheetByName("Sheet1").getRangeList(["A1:B", "C1:C"]).getRanges();
  const target = ss.getSheetByName('Sheet2').getRangeList(["A2", "D2"]).getRanges();
  source.forEach((r, i) => r.copyTo(target[i]));
}

• In this pattern, the values are copied using copyTo, and the column "C" of the destination sheet is not changed.

Note:

• Please choose one of the above scripts for your actual situation.

References:

• map()
• reduce()