Hide formula in google sheet script

hide

Google Sheets: Hide Formulas with IMPORTRANGE

Video time control bar 0:00

Audio volume control bar 0:00 / 0:00 Quality:

Sharing buttons:

In this tutorial, I will show you how to hide a sheet in a Google Sheets spreadsheet using Apps Script. I will also show you how to show or activate a previously hidden sheet.

The function hideSheet() below accepts one parameter, which is the name of the sheet to hide.

First, we get a reference to the sheet by using the getSheetByName(sheetName) method of the Sheet class and then we hide it using the hideSheet() method.

function hideSheet(sheetName) {
 SpreadsheetApp.getActive().getSheetByName(sheetName).hideSheet();
}

To use the function, just call it with the name of the sheet to hide.

// Hide a sheet named "WIP Analysis"
hideSheet("WIP Analysis");

To show a previously hidden sheet, you can either use the showSheet() method or the activate() method. If you use the showSheet() method, then the sheet will be shown but it will not be activated. If on the other hand, you use the activate() method, the sheet will be shown and will also be made active.

function showSheet(sheetName) {
 SpreadsheetApp.getActive().getSheetByName(sheetName).showSheet();
}

function activateSheet(sheetName) {
 SpreadsheetApp.getActive().getSheetByName(sheetName).activate();
}

Conclusion

In this tutorial, I showed you how to hide or show a sheet in Google Sheets using Apps Script.

Thanks for reading!

Stay up to date

Follow me via email to receive actionable tips and other exclusive content. I'll also send you notifications when I publish new content.

By signing up you agree to the Privacy Policy & Terms.

Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!

I want to achieve the following:

1. I do not want other users of the sheet to access the formula bar for certain cells/sheets of the spreadsheet
2. I want to block them from accessing the script editor (do not want to show the code) used for this sheet

Rubén

31.8k9 gold badges65 silver badges151 bronze badges

asked Sep 24, 2016 at 8:19

0

1. I do not want other users of the sheet to access the formula bar for certain cells/sheets of the spreadsheet

This is not possible. You could publish your spreadsheet but this will not allow to edit any content, or to use Google Forms, but this will allow respondents only to submit data, not to view the result of calculations.

To securely hide the formulas they should be in another spreadsheet. You could use IMPORTRANGE or a script to import/export the calculations result. Bear in mind that IMPORTRANGE isn't recalculated immediately and that scripts could be slower than built-in functions.

An alternative is to create a web app that makes the calculations and call it from a custom function.

1. I want to block them from accessing the script editor (do not want to show the code) used for this sheet

It's not possible to block editors to access the Script editor. For details see Scripts Bound to Google Sheets, Docs, or Forms. To prevent that viewers get access to the code, block the spreadsheet for making a copy. For details see Change your sharing settings

Glorfindel

21.2k13 gold badges77 silver badges101 bronze badges

answered Sep 24, 2016 at 12:08

RubénRubén

31.8k9 gold badges65 silver badges151 bronze badges

You can set permissions per cell:

- Click the cell
- Left-mouse click or Ctrl+click -> menu 'Protect range'
- Click 'Set Permissions' in sidebar
- Select Only You

Now the content is still visible. There is a work-around for this, which is quite complicated.

- Create another sheet, called Formulas.
- Protect this sheet the same way above. 
- In your main sheet you can refer to formulas in the other sheet: =Formulas!B1
- Now you can write the formula in this cell B1 in sheet Formulas
  =Sheet1!B1+Sheet1!C1-Sheet1!D1  (where Sheet1 is the name of the first sheet)
- Now hide the Formulas sheet

answered Sep 24, 2016 at 11:26

4

Can you hide a formula in Google Sheets?

How do you hide formulas in a spreadsheet?

How do I remove formula and keep text in Google Sheets?