CODEWITHSUNDEEP
for-loopgoogle-apps-script
Guillaume C
Guillaume C

Reputation: 25

Simple nested for loop problems

I am very new to programming and I am trying to create a short macro on google sheets. I want to copy and paste a list of cells a certain number of times based on variables see here. On the first column you have a list of locations and the second column the number of times they should be pasted into a 3rd column (column F). For example i would like to paste A4 fives times into column F4. Then A5 twice into F8 (F4 + 5 rows) and so one until the end of my list.

I came up with the below code but it currently copies each locations of my list 5 times on the same rows (F4-F8) then repeat the same process on the following 5 rows, and so on. I think the issue is with the order of the loops but i can't figure it out really.

Another problem i have is that It only copies each location 5 times (B1), but i am not sure how to make my variable numShift an array for (B4-B16) so that each locations is copied the correct number of times

Any help would be really appreciated !

function myFunction() {
var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet()
var activeSheet = ss.getActiveSheet()
var numShift = activeSheet.getRange(4,2).getValue()
for (var k=0;k<65;k=k+5){
for (var indexNumLocation=0;indexNumLocation<15;indexNumLocation++){
for (var indexNumShift=0;indexNumShift<numShift;indexNumShift++)    
{var locationRange = activeSheet.getRange(4+indexNumLocation,1).getValue();  
activeSheet.getRange(4+k+indexNumShift,6).setValue(locationRange); 
}
}
}
}

Upvotes: 1

Views: 2528

Answers (1)

Umair Mohammad
Umair Mohammad

Reputation: 4635

Try this 

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var data = [];
  var i, j;
  // get times count for each row from B4 to last row
  var times = sheet.getRange(4, 2, lastRow - 4, 1).getValues();
  // get the data that needs to be copied for each row from A4 to last row
  var values = sheet.getRange(4, 1, lastRow - 4, 1).getValues();
  // loop over each row
  for (i = 4; i <= lastRow; i++) {
    // run loop number of times for that row
    for (j = 1; j <= times[i]; j++) {
      // push the value that needs to be copied in a temporary array
      data.push([values[i][0]]);
    }
  }
  // finally put that array data in sheet starting from F4
  sheet.getRange(4, 6, data.length, 1).setValues(data);
}

Upvotes: 1

Related Questions