Show all googlesheets when clicking outside

Tags: , ,



I am currently working on project where I have two sheets “Sheet 1” & “Sheet 2”. i have developed two html button named “Sheet 1” & Sheet 2. When user clicks “Sheet 1”-> it hides Sheet 2. Similarly, if the user clicks “Sheet 2”-> it hides Sheet 1. Currently button is working as per the below working if user clicks Sheet 1 and “it hides sheet 2” and when when user wants to click on “sheet 2” button, sheet 2 is already hidden and doesn’t show.

function userclicked(userInfo){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws=ss.getSheetByName('Sheet1');
var ws2=ss.getSheetByName('Sheet2');
ws2.hideSheet();
ws.getRange(1,1).setValue(userInfo.Text);
ws.showSheet();
}
function userclicked(userInfo1){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws=ss.getSheetByName('Sheet1');
var ws2=ss.getSheetByName('Sheet2');
ws2.showSheet();
ws2.getRange(1,1).setValue(userInfo1.Text);
ws.hideSheet();
}

How do I show sheet “Sheet2” when Sheet2 button is clicked after clicking sheet1 button that hides “Sheet 2”?

Answer

Explanation:

  • The main issue is that you have 2 functions with the same name.

  • Your general logic is correct, but to make your steps more clear you could check first if a sheet is hidden, and then unhide it:

     if (ws.isSheetHidden()) {
         ws.showSheet();  
     } 
    
  • The last suggestion is optional. You can omit the if statements and use only ws.showSheet() but it should be before ws2.hideSheet(), since you must have at least one sheet open in the UI.

Solution:

Don’t forget to modify the buttons to attach them the new function names:

function userclickedSheet1(userInfo){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws=ss.getSheetByName('Sheet1');
var ws2=ss.getSheetByName('Sheet2');
  
if (ws.isSheetHidden()) {
ws.showSheet();  
}  
ws2.hideSheet();
ws.getRange(1,1).setValue(userInfo.Text);
}


function userclickedSheet2(userInfo1){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws=ss.getSheetByName('Sheet1');
var ws2=ss.getSheetByName('Sheet2');

if (ws2.isSheetHidden()) {
   ws2.showSheet();  
}   
ws.hideSheet();  
ws2.getRange(1,1).setValue(userInfo1.Text);
} 


Source: stackoverflow