Simple nested for loop problems

Question

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); 
}
}
}
}

This loop is for for (var k=0;k<65;k=k+5){ ?

Umair Mohammad
– Umair Mohammad

2018-04-19 08:59:19 +00:00
Commented Apr 19, 2018 at 8:59 — Umair Mohammad
– Umair Mohammad, Commented Apr 19, 2018 at 8:59

Guillaume C · Accepted Answer · 2018-04-20 05:29:08Z

1

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);
}

edited Apr 20, 2018 at 5:29

Guillaume C

252 silver badges7 bronze badges

answered Apr 19, 2018 at 9:00

Umair Mohammad

4,6432 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guillaume C Over a year ago

Thank you so much ! I spent several days trying to figure this out :). For some reason thought the code only works from "location 5" (row 8). I tried to play around with it, but i could not find the way to modified the number of rows for "times" and "values" that is pushed in data

Umair Mohammad Over a year ago

I take your words and I have approved your edit! stackoverflow.com/help/someone-answers

Collectives™ on Stack Overflow

Simple nested for loop problems

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related