How to update each row in google app script from from another sheet-CodePudding

I have the following code which pulls a value in column[3] from ZONE sheet to the active sheet when the zonelist value = zone value in each of different sheets. The cell value is moved to column 56 of the job sheet. I require the script to check the range from 2 to lastRow in the Job sheet and then set the corresponding value from the zone sheet to the respective cell in the job sheet. When the script runs but it is listing the same value in all rows of column 56 in the job sheet. I require it to transfer the cell value which corresponds to the matching zone.

function UpdateZoneRate(){
   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('JOBS'); 
   
  var jobSheet = ss.getSheetByName('JOBS');
  var zoneSheet = SpreadsheetApp.openById(
    "1jbG_PLWU_eXfQTkwUn1krtYfzniw7HwnwnunhohzL61").getSheetByName('ZONE'); 
  var lastzone = zoneSheet.getLastRow();
  var zoneList = zoneSheet.getRange(2,1,lastzone,4).getValues();
  var newProd = jobSheet.getLastRow();
  var productCode = jobSheet.getRange(2,16,newProd,1).getValue();
  var newZone = jobSheet.getLastRow();
  var zone = jobSheet.getRange(2,53,newZone,1).getValue();
 
  for (j = 0;j <zoneList.length;j  ){
      if (zoneList[j][0] == zone && productCode.match(/^A/i))
        var aggZone = zoneList[j][3];
        jobSheet.getRange(2,56,newZone,1).setValue(aggZone)}
                             
   }

SAMPLE VALUES FROM ZONESHEET ZONE1 2 ZONE2 4 ZONE3 6

CURRENT OUTPUT FROM SCRIPT ZONE1 2 ZONE2 2 ZONE2 2

CORRECT OUTPUT REQUIRED FROM THE SCRIPT ZONE1 2 ZONE2 4 ZONE2 4

Thank you for any assistance

CodePudding user response：

From your following reply,

it should pull the value from column "D". When the script runs it should match the cell value of column BA in the JOBS sheet with the cell value in column A of the ZONE, then move the corresponding value in column D to column BD of the JOB sheet.

Is your expected script as follows?

Sample script:

In this case, please copy and paste the following script to the script editor of the Spreadsheet including "JOBS" sheet.

function sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var jobSheet = ss.getSheetByName('JOBS');
  var zoneSheet = SpreadsheetApp.openById("1jbG_PLWU_eXfQTkwUn1krtYfzniw7HwnwnunhohzL61").getSheetByName('ZONE');
  var obj = zoneSheet.getRange("A2:D"   zoneSheet.getLastRow()).getValues().reduce((o, r) => (o[r[0]] = r[3], o), {});
  var range = jobSheet.getRange("BA2:BD"   jobSheet.getLastRow());
  var values = range.getValues().map(r => [obj[r[0]] || null]);
  range.offset(0, 3, values.length, 1).setValues(values);
}

When this script is run, the values are retrieved from "JOBS" sheet in the active Spreadsheet and "ZONE" sheet of another Spreadsheet, and the column "A" of "JOBS" sheet and the column "BA" of "ZONE" sheet are compared. And then, the values are copied from column "D" of "JOBS" sheet to column "BD" of "ZONE" sheet.