select multiple sheets with the same range-CodePudding

I have the code below that works perfectly, but I need to alter it to get data from the same range (D5) but from multiple sheets. At the moment, it gets the data from 'Cancellations' only. How would I get it to select D5 from say 'Sheet2', or 'Sheet3' as well. I have included half of the code from the function I currently have. As you can see it's very repatative.

    function transferList() {

///////////////////////////////////////////Export value 1/////////////////////////////////////////////////////
  var columnIndex = 1; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Cancellations");
  var sourceData = sourceSheet.getRange("D5").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(2,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(3,columnIndex);
  targetRange.setValue(sourceData);
  
  var columnIndex = 1; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Unanswered");
  var sourceData = sourceSheet.getRange("D5").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(2,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(3,columnIndex);
  targetRange.setValue(sourceData);

  var columnIndex = 1; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Queries");
  var sourceData = sourceSheet.getRange("D5").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(2,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(3,columnIndex);
  targetRange.setValue(sourceData);





///////////////////////////////////////////Export value 2/////////////////////////////////////////////////////
  var columnIndex = 4; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Cancellations");
  var sourceData = sourceSheet.getRange("D6").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

  var columnIndex = 4; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Unanswered");
  var sourceData = sourceSheet.getRange("D6").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

  var columnIndex = 4; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Queries");
  var sourceData = sourceSheet.getRange("D6").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);


///////////////////////////////////////////Export value 3////////////////////////////////////////////////////////

 var columnIndex = 7; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Cancellations");
  var sourceData = sourceSheet.getRange("D7").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

   var columnIndex = 7; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Unanswered");
  var sourceData = sourceSheet.getRange("D7").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

   var columnIndex = 7; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Queries");
  var sourceData = sourceSheet.getRange("D7").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

///////////////////////////////////////////Export value 4///////////////////////////////////////////////////////////

 var columnIndex = 10; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Cancellations");
  var sourceData = sourceSheet.getRange("D9").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

  var columnIndex = 10; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Unanswered");
  var sourceData = sourceSheet.getRange("D9").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

var columnIndex = 10; // Column 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Queries");
  var sourceData = sourceSheet.getRange("D9").getValue();
  var targetSS = ss.getSheetByName("Deduction Breakdown");
  var a1Range = targetSS.getRange(5,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(6,columnIndex);
  targetRange.setValue(sourceData);

CodePudding user response：

Here is a full script that should do what you are asking:

/** @OnlyCurrentDoc */

function transferList() {

  var columnIndex = 1; // Column
  var columnIndexIncr = 3; //columnIndex increment per each target range
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheets = ["Cancellations", "Unanswered", "Queries"]; //Sheet names
  var targetSS = ss.getSheetByName("Deduction Breakdown"); //Target sheet
  var targetRanges = ["D5", "D6", "D7", "D9"]; //Target ranges
  var sourceData;
  var a1Range;
  var a1Row;
  var targetRange;

  for(var range of targetRanges) {
    for(var i in sourceSheets) {
      if(range == "D5") {a1Row = 2} else {a1Row = 5};
      sourceData = ss.getSheetByName(sourceSheets[i]).getRange(range).getValue();
      a1Range = targetSS.getRange(a1Row, columnIndex);
      if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
      else targetRange = targetSS.getRange((a1Row 1),columnIndex);
      targetRange.setValue(sourceData);
    }
    columnIndex  = columnIndexIncr;
  };

};

Please let me know if you have any issues with this. I tested this script in a test sheet and it should work. Please let me know if this was not the intended result

CodePudding user response：

multiple sheets same range

function transferList() {
  const ss = SpreadsheetApp.getActive();
  const dl = [{srcname:'Cancellations',srcrng:'D5',dstname:'Deduction Breakdown',dstrng:'A2'}];//fill in the remaining options
  dl.forEach(o => {
    let ssh = ss.getSheetByName(o.srcname);
    let srg = ssh.getRange(o.srcrng);
    let sda = srg.getValue();
    let dsh = ss.getSheetByName(o.dstname);
    let drg = dsh.getRange(o.dstrng);
    if(drg.offset(1,0).getValue() !== "") {
      drg = drg,getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
    } else {
      drg = drg.offset(1,0);
    }
    drg.setValue(sda);
  })
}