Google-sheets – Having Problems with SUMIF in Google Sheets

Google Sheets does indeed have Sheet-level named ranges internally, and they are almost fully functional. :-)

The challenge is that as of 16/2/16 they can only be created by Copying a Sheet with an existing NamedRange and then Google Sheets create the parallel sheet-level named Ranges (in a similar way that excel does when you copy a worksheet containing Named Ranges).

Unfortunately you cannot create them through the Named Ranges window or corresponding Script function.

However, once you have created the sheet-level named ranges, they perform exactly as you would expect, being local to the page, or you can use the Global (Workbook) version (where a specific sheet does not have an overriding sheet-level range).

I have created a Google Sheet that shows how you can use Sheet-level named-Ranges both in formulas and Google Script. You can see it here.

I have also posted a more detailed answer in Google Forums.

At this time it's possible to protect individual sheets from editing but it's not possible to protect them from viewing.

One workaround is to create create a spreadsheet for each user and add to that spreadsheet only the information that they should be able to view. It's worth to note that IMPORTRANGE could be useful for certain cases, in other you could require the use of Google Apps Script.