Match values from one list to other on different sheet and copy a single cell GAS

1
votes

need help over an script that is not working on google app script. I have a sheet1 where colA is a date and colB is an ID. On sheet2 i have same ID but could be at any row (not in order). So, the script should find this ID and copy on col P (sheet2) the date on colA (sheet1). Here the script that doesn't make nothing. Thanks in advance for any help. 

function CopyDate() {
  ss = SpreadsheetApp.openById('Name');
  sheetA = ss.getSheetByName('Sheet1');
  dataA = sheetA.getRange('A:B').getValues();
  sheetB = ss.getSheetByName('Sheet2');
  dataB = sheetB.getRange('A:P').getValues();
  for(var i = 0; i > sheetA.getLastRow(); i++) {
    if (dataA[2][i] == dataB[1][i]) {
      var value = sheetA.getRange(i+1, 1).getValue();
      sheetB.getRange(i+1, 16).setValue(value);
    }
  }
}
1
google-apps-scriptgoogle-sheets
One more thing: the ID on sheet1 is on col B but on sheet2 on colA – MarinaMontero

1 Answers

0
votes
  • "Sheet1" has Date and ID in the column "A" and "B", respectively.
  • "Sheet2" has ID in the column "A".
  • You want to copy Date of "Sheet1" to the column "P" of "Sheet2" when IDs of "Sheet1" and "Sheet2" are the same.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification?

Modification points:

  • About for(var i = 0; i > sheetA.getLastRow(); i++){, in this case, the for loop is not used, because of i > sheetA.getLastRow().
  • dataA and dataB are 2 dimentional array like [[columnA of row1, columnB of row1],[columnA of row2, columnB of row2],,,]. So at dataA[2][i] == dataB[1][i], the row 2 of "Sheet1" and row 1 of "Sheet2" are compared.
  • When setValue() is used in a loop, the cost become very high. So please put the values using setValues() after created the putting value.
  • In your case, when var dataA = sheetA.getRange(1, 1, sheetA.getLastRow(), 2).getValues() instead of sheetA.getRange('A:B').getValues() is used, the process cost can be reduced.

When above points are reflected to your script, it becomes as follows.

Modified script:

function CopyDate() {
  var ss = SpreadsheetApp.openById('Name'); // If you want to use openById(), please replace the Spreadsheet ID to ``Name``.
  var sheetA = ss.getSheetByName('Sheet1');
  var dataA = sheetA.getRange(1, 1, sheetA.getLastRow(), 2).getValues();
  var sheetB = ss.getSheetByName('Sheet2');
  var dataB = sheetB.getRange(1, 1, sheetB.getLastRow(), 1).getValues();
  var values = dataB.map(function(e) {
    var t = dataA.filter(function(f) {return f[1] == e[0]});
    return t.length > 0 ? [t[0][0]] : [""];
  });
  sheetB.getRange(1, 16, values.length, 1).setValues(values); // 16 means the column "P".
}

Note:

  • In your script, Name can be seen in ss= SpreadsheetApp.openById( 'Name');. If you are using the spreadsheet name, please use the spreadsheet ID. If I misunderstood this, please ignore.
  • If you don't want to reduce the process cost, I apologize.

References:

If this was not the result you want, I apologize. At that time, in order to correctly understand your situation, can you provide a sample spreadsheet? Of course, please remove your personal information from it. By this, I would like to modify the script.