Google Script - Copy values from column to another sheet matching adjacent cell

Question

I'm trying to adapt this solution to my problem, but it's not working: Script to match column values and copy data

Basically I have a list of people in sheet "data", and some of the same people are listed in the sheet "values". I need to populate last column in sheet "data" when I run the script with new values posted in "values" sheet, column B, in the same row as the person name. If it possible I also need to enter a 0 value whenever this person is not present in "values" sheet (this is not coded yet, any hint is welcome).

Example:

values sheet

| A       | B   |
| ------- | --- |
| John    | 4   |
| Anna    | 2   |
| Bill    | 2   |
| Valery  | 5   |
| Joe     | 6   |

data sheet

| A       | B   |
| ------- | --- |
| John    | 4   |
| Alan    | 0   |  << not present in values sheet
| Robert  | 0   |  << not present in values sheet
| Anna    | 2   |
| Jessica | 0   |  << not present in values sheet
| Bill    | 2   |
| Valery  | 5   |
| Joe     | 6   |

In this example the column B in data sheet is populated with values, but I'd like to populate the next free column with new values from first sheet whenever I launch the script. Hope this can clarify better.

This is my code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss.getSheetByName('values');
  var sh1lastRow = sh1.getLastRow();
  var sh1AValues = sh1.getRange(1, 1, sh1lastRow, 1).getValues().flat();
  var sh1BValues = sh1.getRange(1, 2, sh1lastRow, 1).getValues().flat();

  var sh2 = ss.getSheetByName('data');
  var sh2lastRow = sh2.getLastRow();
  var sh2lastCol = sh2.getLastColumn();
  var sh2AValues = sh2.getRange(1, 1, sh2lastRow, 1).getValues().flat();
  var sh2xRange = sh2.getRange(1, sh2lastCol, sh2lastRow, 1);


  var output = sh1AValues.map(row => {
    var index = sh1BValues.indexOf(row);
    if(index >= 0)
      return [sh1BValues[index]];
    return [];
  });

  sh2xRange.setValues(output);
}

but the result is an error: "Exception: The number of rows in the data does not match the number of rows in the range. The data has 25 but the range has 31." Looks like I'm not correctly handling the code :( Any help?

SOLUTION:

(thanks to Logan)

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss.getSheetByName('values');
  var sh1lastRow = sh1.getLastRow();
  var sh1AValues = sh1.getRange(1, 1, sh1lastRow, 1).getValues().flat();
  var sh1BValues = sh1.getRange(1, 2, sh1lastRow, 1).getValues().flat();

  var sh2 = ss.getSheetByName('data');
  var sh2lastRow = sh2.getLastRow();
  var sh2lastCol = sh2.getLastColumn() +1;
  var sh2AValues = sh2.getRange(1, 1, sh2lastRow, 1).getValues().flat();
  var sh2xRange = sh2.getRange(1, sh2lastCol, sh2lastRow,1);

  var output = sh2AValues.map(row => {
    var index = sh1AValues.indexOf(row);
    if(index >= 0)
      return [sh1BValues[index]];
    return [0];
  });

  sh2xRange.setValues(output);
}

Answer 1

Try:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss.getSheetByName('values');
  var sh1lastRow = sh1.getLastRow();
  var sh1AValues = sh1.getRange(1, 1, sh1lastRow, 1).getValues().flat();
  var sh1BValues = sh1.getRange(1, 2, sh1lastRow, 1).getValues().flat();

  var sh2 = ss.getSheetByName('data');
  var sh2lastRow = sh2.getLastRow();
  var sh2AValues = sh2.getRange(1, 1, sh2lastRow, 1).getValues().flat();
  var sh2xRange = sh2.getRange(1, 2, sh2lastRow,1);

  var output = sh2AValues.map(row => {
    var index = sh1AValues.indexOf(row);
    if(index >= 0)
      return [sh1BValues[index]];
    return [0];
  });

  sh2xRange.setValues(output);
}

Result: