Creating a loop that modifies row after row in google Scripts

Question

rookie coder here. I'm working in Google Scripts for a project and here's what I'm trying to do.

Essentially, I am trying to access a specific spreadsheet (called "Top Sheet") through an AICP address that has been entered in cell K2 of my "2013" spreadsheet. The Information that I am accessing in the "Top Sheet" spreadsheet is a single number located in cell K46. Then, I am taking the value of cell K46 (located in "Top Sheet") and placing it into a new cell (N2) in the "2013" spreadsheet.

Here's what that looks like (and it works).

 function GetJobActuals() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();  
   var s2 =SpreadsheetApp.openById(ss.getSheetByName("2013").getRange("K2:K2").getValue());
   var v2 = s2.getSheetByName("Top Sheet").getRange("K46:K46").getValue();
   ss.getSheetByName("2013").getRange("N2:N2").setValue(v2);
   var s3 = SpreadsheetApp.openById(ss.getSheetByName("2013").getRange("K3:K3").getValue());
   var v3 = s3.getSheetByName("Top Sheet").getRange("K46:K46").getValue();
   ss.getSheetByName("2013").getRange("N3:N3").setValue(v3);

What I'm trying to do now, it make this happen for all of the preceding cells in the "2013" spreadsheet. For example, this code works for one number. Cell K2 in "2013" tells it to retrieve the value of cell K46 in "Top Sheet" and then returns with that value and places it into a new cell, N2.

How would I loop this so that this process would happen for the cell range of K2 - K60 and the repopulate the new cell range of N2 - N60?

Man I hope this makes sense, any and all help is appreciated. And again, the above code works, just trying to make a loop that can handle more than one thing at a time.

Mogsdad · Accepted Answer · 2013-07-05 17:36:24Z

The answer should become clear to you if you use Sheet.getRange(row,column) instead of Sheet.getRange(a1Notation). By using row and column numbers instead of A1 notation, you can apply a for loop to acheive your goal.

ss.getSheetByName("2013").getRange("N3:N3").setValue(v3);

  becomes

ss.getSheetByName("2013").getRange(3,14).setValue(v3);
                                   ^^^^
                        Row = 3, Column = 14 ("N")

So you can then iterate over rows:

for ( row=2; row<=60; row++) {
  ...
  var s3 = SpreadsheetApp.openById(ss.getSheetByName("2013").getRange(row,11).getValue());
  ...
  ss.getSheetByName("2013").getRange(row,14).setValue(v3);
  ...
}

Details left to you, but that's the basic structure for your loop. There are more efficient ways to make this work, but since you're new to this, worry about that after you get the basics working.

Collectives™ on Stack Overflow

Creating a loop that modifies row after row in google Scripts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related