Google Apps Script. Setting an active sheet (focus) in another spreadsheet.

Question

I have this script which deletes all but the first sheet in another spreadsheet. It works fine, but if in the other spreadsheet the focus is not in the first sheet, the other spreadsheet crashes and has to be reloaded. Not a problem, everything still works OK, but a nuisance. How can I make the first sheet have input focus before deleting all but the first sheet?

          var numsh = dest_ss.getNumSheets();
          for (var i = 0; i < numsh-1; i++) {
          dest_ss.deleteSheet(dest_ss.getSheets()[dest_ss.getNumSheets() - 1]);
          }

So you've got 2 separate spreadsheet, and you delete all but the first sheet on Spreadsheet 1 from Spreadsheet 2. But if someone is looking at some sheet who's gonna be delete on Spreadsheet 1, Spreadsheet 1 chrash and has to be reload. And you want to set the focus from the delete sheet to the first sheet to avoid those crash. Am I correct ? — Pierre-Marie Richard, Oct 12 '17 at 10:00
Yes that is correct. From Spreadsheet 1 I'm deleting all but the first sheet of Speadsheet 2. — Barry Allen, Oct 13 '17 at 11:12

score 2 · Answer 1 · answered Oct 12 '17 at 11:35

2

You can make the first sheet the active worksheet using

dest_ss.setActiveSheet(dest_ss.getSheets()[0]);

answered Oct 12 '17 at 11:35

Robin Gertenbach

10,316
3
25
37

I think that I tried this but still got the error "Unable to load file, try to load it again or send an error report." Just tried again and got the same error. – Barry Allen Oct 13 '17 at 11:16

score 0 · Answer 2 · answered Oct 13 '17 at 07:40

0

You should count backward and not update the index value. Doing so you won't need to care about the active sheet.

Try like this :

var numsh = dest_ss.getNumSheets();
for (var i = numsh-1; i >0; i--) {
  dest_ss.deleteSheet(dest_ss.getSheets()[i]);
}

answered Oct 13 '17 at 07:40

Serge insas

45,904
7
105
131

Thanks for this but it still gives the same error:- "Unable to load file, try to load it again or send an error report." If I manually select the first sheet then there is no error. – Barry Allen Oct 13 '17 at 11:29
I think I'll use your code. It looks more efficient. Many thanks. I'll just have to remember to select the first sheet before running. – Barry Allen Oct 13 '17 at 11:43
from my experience it is a good idea to add a small pause between each deleteSheet call to prevent freezing the browser page, I use a 400 mS sleep using Utilities.sleep(400); maybe you can try if it solves the problem for you as well. – Serge insas Oct 13 '17 at 12:58
please have a look at this post where this specific aspect is described : https://stackoverflow.com/questions/11142109/how-to-use-utilities-sleep-function/11142322#11142322 btw, it uses another method to delete sheets because at the time I wrote it the method you are using didn't exist yet ;) – Serge insas Oct 13 '17 at 13:18
The 400 mS pause has solved the problem. Many thanks. I tried 200 mS but still got the error. – Barry Allen Oct 13 '17 at 20:36
Glad to hear that ! – Serge insas Oct 13 '17 at 22:04

score -2 · Answer 3 · edited Oct 13 '17 at 07:06

-2

Try this code

var numsh = dest_ss.getNumSheets();
for (var i = 0; i < numsh; i++){
       if(dest_ss.getSheets()[i].getName()!= "YOUR SHEET NAME WHICH YOU DONOT WANT TO DELETE"){
          dest_ss.deleteSheet(dest_ss.getSheets()[i]);
       }
 }

edited Oct 13 '17 at 07:06

Vega

27,856
27
95
103

answered Oct 13 '17 at 01:03

Tehsin Khan

1

I get an error of 'Cannot call method "getName" of undefined.' at this line:- if(dest_ss.getSheets()[i].getName()!= "Info"){ – Barry Allen Oct 13 '17 at 11:34
You can't put code that is harmful for the user ... what if they didn't spot the deleteSheet option? – Andrea Moro Jul 30 '21 at 09:25

Google Apps Script. Setting an active sheet (focus) in another spreadsheet.

3 Answers3