Google Apps Script to find and update rows in target sheet by unique ID and add unique rows if unique ID is not in the target sheet

user_16249743 0
Viewed 939

Good day folks! I have this codes in which I want to combine into one, but having trouble to do so.

This is the first code in which the data is copied from source sheet to target sheet added to the after the last row with data

function DE() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); // activates the opened document
  let sourceSheet = spreadSheet.getSheetByName('Support Sheet'); // selects the sheet where the data will be coming from
  let sourceSheet2 = spreadSheet.getSheetByName('Data Entry'); // selects the sheet where the sheet name contains

  let sourceRange = sourceSheet.getDataRange(); 
  let sourceRange2 = sourceSheet2.getDataRange();
  let sourceValues = sourceRange.getValues();
  let sourceValues2 = sourceRange2.getValues();
  let sheetName = sourceValues2[1][1];
  sourceValues = sourceValues.slice(1).map(row => row.slice(13,13+10));

  let rowCount = sourceValues.length;
  let columnCount = sourceValues[0].length;

  let targetSheet = spreadSheet.getSheetByName(sheetName);

  let lastRow = targetSheet.getLastRow() + 1;

  let targetRange = targetSheet.getRange(lastRow,1,rowCount,columnCount);

  targetRange.setValues(sourceValues);
}

And this is the code that I saw here which works perfectly in my spreadsheet, where this one updates the column B if there were changes based on unique ID (column A)

function updateEntrees() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Support Sheet');
  var rg1a=sh1.getRange(2,1,sh1.getLastRow()-1,1);
  var vA1a=rg1a.getValues();
  var rg1b=sh1.getRange(2,2,sh1.getLastRow()-1,1);
  var vA1b=rg1b.getValues();
  var sh2=ss.getSheetByName('Target Sheet');
  var rg2a=sh2.getRange(2,1,sh2.getLastRow()-1,1);
  var vA2a=rg2a.getValues();
  var rg2b=sh2.getRange(2,2,sh2.getLastRow()-1,1);
  var vA2b=rg2b.getValues();
  for(var i=0;i<vA1a.length;i++) {
    for(var j=0;j<vA2a.length;j++) {
      if(vA1a[i][0]==vA2a[j][0]) {
        vA2b[j][0]=vA1b[i][0]
      }
    }
  }
  rg2b.setValues(vA2b);
}

Now I am wondering how I am going to combine this 2, where if the source sheet has unique ID that needs updating it will update the target sheet and if there is a new unique ID, it will just add the data at the bottom

google-apps-script google-sheets javascript
1 Answers

I believe your goal is as follows.

  • You have 2 sheets of the source sheet and the target sheet.
  • You want to update and append values from the source sheet to the target sheet by checking the column "A" of both sheets.

In this case, how about the following modified script?

Modified script:

function myFunction() {
  // 1. Retrieve values from the source and target sheets.
  var ss = SpreadsheetApp.getActive();
  var [srcSheet, targetSheet] = ['Support Sheet', 'Target Sheet'].map(s => ss.getSheetByName(s));
  var [srcValues, targetValues] = [srcSheet, targetSheet].map(s => s.getLastRow() == 1 ? [] : s.getRange("A2:B" + s.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);
}
  • From your script, this sample script supposes that your 1st row of both sheets is the header row. Please be careful about this.

Note:

  • I proposed the above script by guessing your Spreadsheet from your script and question. When this script is not useful for your situation, can you provide the sample Spreadsheet? By this, I would like to confirm it.

References:

Added 1:

From the following replying,

this works fine, but the data being added to the target sheet is just column A and B, here is the sample sheet: docs.google.com/spreadsheets/d/… where the range from source sheet that needs to transfer to target sheet is N:X

How about the following sample script?

Modified script:

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, "N2:X"], [targetSheet, "A2:K"]].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);
}

Added 2:

About your following new question,

what if I just want to update the Column B in target sheet? and other column will stay the same?

How about the following script?

Modified script:

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, "N2:X"], [targetSheet, "A2:K"]].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.
  targetSheet.getRange(2, 1, updatedValues.length, updatedValues[0].length).setValues(updatedValues);
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, appendValues.length, appendValues[0].length).setValues(appendValues);
}
user_71086530
Related