Pass loop value along with a string

Question

I want to move among the sheets within the same workbook, my sheet names are like 'Order 1', 'Order 2', 'Order 3'........so on. I want to take the value from loop along with 'Order' string like 'Order [i]' and want to paste some formula that also takes some value from the loop like ={Link!B[[i]+2]}

I tried with this following code but can't be succeeded.

function Order() {
  var spreadsheet = SpreadsheetApp.getActive();
  for (var i = 1; i <= 10; i++) {
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Order [i]'));
    spreadsheet.getRange('B3').activate();
    spreadsheet.getCurrentCell().setValue('={Link!B[[i]+2]}');;
  }  
};

Diego · Accepted Answer · 2019-11-14 04:04:43Z

1

When you specify 'Order [i]', your script looks for a sheet called "Order [i]". Instead, you should write 'Order ' + i.

Similarly, '={Link!B[[i]+2]}' will return "={Link!B[[i]+2]}", just as you wrote it. Instead, write '={Link!B' + (i+2) + '}'.

Also, you don't need the .activate() or .setActiveSheet() calls. You can simply get the range and then immediately set the value. You're getting the sheet already with the .getSheetByName() method. In fact, you link all of it together.

function Order() {
  var spreadsheet = SpreadsheetApp.getActive();
  for (var i = 1; i <= 10; i++) {
    spreadsheet.getSheetByName('Order ' + i)
      .getRange('B3')
      .setValue('={Link!B' + (i+2) + '}');
  }  
};

answered Nov 14, 2019 at 4:04

Diego

9,5812 gold badges24 silver badges35 bronze badges

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

4 Comments

Mubashir Ali Over a year ago

Thanks! I have changed something from your code and it works

Diego Over a year ago

@MubashirAli Great. What did you change?

Mubashir Ali Over a year ago

Diego Over a year ago

@MubashirAli Thanks for sharing it. As I mentioned, you don't need those activation calls, but as long as it work it's fine :) Please consider marking the answer as accepted if it worked for you.

ethandjay · Accepted Answer · 2019-11-14 04:01:59Z

0

The string 'Order [i]' will always just be 'Order [i]', as everything is wrapped in quotes so the computer is interpreting it literally. You want to use something like 'Order ' + i, which will evaluate what i is and then append it to 'Order'.

In the second example, you might want something like '={Link!' + B[i+2] + '}'

answered Nov 14, 2019 at 4:01

ethandjay

617 bronze badges

1 Comment

Mubashir Ali Over a year ago

Thanks, I have made it. People from stack overflow community are great. I have also posted this question on the Google community and have nothing from there for 2 days.

Collectives™ on Stack Overflow

Pass loop value along with a string

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related