I am trying to:

1. Update existing data from sheet A to sheet B
2. Append all new content from sheet A as new rows in sheet B.

Using the code from https://stackoverflow.com/a/60266697/19427703 to achieve the first part of the objective, I'm still unable to build the second part.

  function PRINT(

  fromSht = SpreadsheetApp.getActive().getSheetByName('A'), //fromSht -Sheet to import from
  toSht = SpreadsheetApp.getActive().getSheetByName('B'), //toSht -Sheet to import to
  fromCompCol = 1, //fromCompCol -Column number of fromSht to compare
  toCompCol = 1, //toCompCol -Column number of toSht to compare
  fromCol = 2, //fromCol -Column number of fromSht to get result
  toCol = 2 //toCol -Column number of toSht to get result

) {

  const toShtLr = toSht.getLastRow();
  const toCompArr = toSht.getRange(1, toCompCol, toShtLr, 1).getValues();   
  const fromArr = fromSht.getDataRange().getValues();
  
  fromCompCol--;
  fromCol--;

  const obj1 = fromArr.reduce((obj, row) => {
    let el = row[fromCompCol];
    el in obj ? null : (obj[el] = row[fromCol]);    
    return obj;

  }, );
  
  toSht
    .getRange(1, toCol, toShtLr, 1)
    .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));

}

As a coding beginner, any help or tips would be appreciated.

Sheet A

Sheet B

What i can achieve now

Expecting (order isn't important)

EDIT

This is the final working code. Thanks to @Tanaike for the help.

function PRINT(

  fromSht = SpreadsheetApp.getActive().getSheetByName('A'), //fromSht -Sheet to import from
  toSht = SpreadsheetApp.getActive().getSheetByName('B'), //toSht -Sheet to import to
  fromCompCol = 1, //fromCompCol -Column number of fromSht to compare
  toCompCol = 1, //toCompCol -Column number of toSht to compare
  fromCol = 2, //fromCol -Column number of fromSht to get result
  toCol = 2 //toCol -Column number of toSht to get result

) {

  const toShtLr = toSht.getLastRow();
  const toCompArr = toSht.getRange(1, toCompCol, toShtLr, 2).getValues();
  const fromArr = fromSht.getDataRange().getValues();

  const srcObj = fromArr.reduce((o, [a, b]) => (o[a] = [a, b], o), {});
  const updateValues = toCompArr.map(([a, b]) => {
      if (srcObj[a]) {
        const temp = srcObj[a];
        delete srcObj[a];
        return temp;
  }
      return [a, b];
});

  const diffValues = Object.values(srcObj);
  const values = [...updateValues, ...diffValues];
  toSht.getRange(1, 1, values.length, 2).setValues(values);

}

CodePudding user response：

Modification points:

• From your showing sample input and output images and your script, I thought that for the destination sheet, it is required to check the update rows and the different rows.
  • In your script, only the update rows are checked. I thought that this might be the reason for your issue.
• In your script, about fromArr.reduce((obj, row) => {, }, ); is required to be modified. In this case, the initial value of {} is required to be used. Please be careful about this.

When these points are reflected in your script, how about the following modification?

From:

fromCompCol--;
fromCol--;

const obj1 = fromArr.reduce((obj, row) => {
  let el = row[fromCompCol];
  el in obj ? null : (obj[el] = row[fromCol]);    
  return obj;

}, );

toSht
  .getRange(1, toCol, toShtLr, 1)
  .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));

To:

const srcObj = fromArr.reduce((o, [a, b]) => (o[a] = [a, b], o), {});
const updateValues = toCompArr.map(([a]) => {
  if (srcObj[a]) {
    const temp = srcObj[a];
    delete srcObj[a];
    return temp;
  }
  return [a, null];
});
const diffValues = Object.values(srcObj);
const values = [...updateValues, ...diffValues];
toSht.getRange(1, 1, values.length, 2).setValues(values);

References:

• map()
• reduce()

Added:

Frmo your following new request,

Works very well so far! Now, the only problem is when there's no data in sheet A to compare, it keeps the name of the object in sheet B but delete the number linked to it. I tried to change the "return [a, null];" part of the code (also deleting it completly) but I keep getting error message.

Although, unfortunately, I'm not sure whether I could correctly understand your new request, how about the following modification?

From:

const toShtLr = toSht.getLastRow();
const toCompArr = toSht.getRange(1, toCompCol, toShtLr, 1).getValues();   
const fromArr = fromSht.getDataRange().getValues();

fromCompCol--;
fromCol--;

const obj1 = fromArr.reduce((obj, row) => {
  let el = row[fromCompCol];
  el in obj ? null : (obj[el] = row[fromCol]);    
  return obj;

}, );

toSht
  .getRange(1, toCol, toShtLr, 1)
  .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));

To:

const toShtLr = toSht.getLastRow();
const toCompArr = toSht.getRange(1, toCompCol, toShtLr, 2).getValues();
const fromArr = fromSht.getDataRange().getValues();

const srcObj = fromArr.reduce((o, [a, b]) => (o[a] = [a, b], o), {});
const updateValues = toCompArr.map(([a, b]) => {
  if (srcObj[a]) {
    const temp = srcObj[a];
    delete srcObj[a];
    return temp;
  }
  return [a, b];
});
const diffValues = Object.values(srcObj);
const values = [...updateValues, ...diffValues];
toSht.getRange(1, 1, values.length, 2).setValues(values);