Google Sheets activate a cell within a loop

Question

I have a loop that does what I want, but I would like to set an active cell in each sheet so I don't have to click it each time as I cycle through.

I've tried two ways that seem to make sense, but they both only work on the last sheet in the loop:-

function setDraft() {

//msg box to confirm relevant sheets are hidden, thus excluded from code
  var response = Browser.msgBox("SET AS DRAFT","Have you hidden sheets you don't want marked as draft?", Browser.Buttons.YES_NO);
  if(response=="no")
    return;
  else if(response=="cancel")
    return;
  else

//loop and code for Visible sheets  
  var ss = SpreadsheetApp.getActive();
  var allsheets = ss.getSheets();

  for (var s in allsheets){
  var sheet=allsheets[s]
  var date = sheet.getRange('I1')

  if (sheet.isSheetHidden()!= true) {   
//sheet.setActiveRange(date);
  sheet.setActiveSelection(date);
  sheet.getRange('I5').setValue('DRAFT');
       }    
    }
}

Can anyone please let me know where I'm going wrong?

Thanks in advance!

Maybe you missed to put the statements after the last else inside a block. Have you tried to use the debugging tool in the Google Apps Script Editor? — Rubén, Jun 01 '18 at 00:35

Brian · Answer 1 · 2018-06-01T12:20:31.193

0

Using for...in in JS can lead to problems (more on that in this post). Second, try using the built in UI class through the SpreadsheetApp class rather than the Browser buttons.

function setDraft() {
  var ui = SpreadsheetApp.getUi();
  var allsheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  //msg box to confirm relevant sheets are hidden, thus excluded from code
  var response = ui.alert("SET AS DRAFT","Have you hidden sheets you don't want marked as draft?", ui.ButtonSet.YES_NO);
  if(response == ui.Button.NO) {
    return;
  } else {
    for (var s=0; s<allsheets.length; s++){
      var sheet=allsheets[s]
      var date = sheet.getRange('I1')

      if (sheet.isSheetHidden()!= true) {   
        sheet.setActiveSelection(date);
        sheet.getRange('I5').setValue('DRAFT');
      }    
    }
  }
}

edited Jun 01 '18 at 12:20

answered Jun 01 '18 at 00:36

Brian

4,274
2
27
55

That doesn't seem to work either... Where are the curly braces missing, ? after "if(response=="no")", but then I can't work out where they close? – Nish1585 Jun 01 '18 at 00:56
I updated my answer to include the curly braces. Also, consider using the UI class rather than Browser. – Brian Jun 01 '18 at 01:41
I'm getting allsheets is not defined, so I tried to define with: var ss = SpreadsheetApp.getActive(); var allsheets = ss.getSheets(); but that still isn't leaving 'I7' as the active cell in each sheet either? Thank you for taking the time to help. – Nish1585 Jun 01 '18 at 02:34
I added the variable (my fault). It activates I7 when the script runs and changes the values. It doesn't necessarily leave the cell selected when it runs. – Brian Jun 01 '18 at 12:22

score 0 · Answer 2 · answered Jun 01 '18 at 04:51

Use the sheet.activate() or range.activate() methods. Use sheet method if you only care to go to a specific sheet, but if you want the select a specific cell then use it with range. I have not tested with range, however, I know that if you use sheet.activate() then you will be brought to that sheet.

Google Sheets activate a cell within a loop

2 Answers2