Google-sheets – How to circumvent protected sheets to expand or collapse groups and rows in Google Sheets

google sheetsgoogle-apps-script

A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns according to category and month. The only cells which should be edited are the planned expense/income values. It makes sense to protect the sheet except for those cells. However, by protecting the sheet, I am unable to collapse or expand categories and months. Is there a workaround to retain the design and experience I would like to implement?

Best Answer

I ran into this same issue. I was unable to find a suitable solution to the problem for myself, however I did find a workaround that may work for you.

I am working on a project that requires protecting the spreadsheet except for select cells, where end users should not have the ability to edit protected cells without exception. This means strict protection (no warning) is the only option. What you can possibly use as a workaround is to change the protection settings to "Can edit (with warnings)". For these steps, I am assuming, as you said, that the entire sheet is protected except for specific cells.

Instructions:

Open your Google Sheet.
In the menu ribbon at the top click the "Data" button.
In the drop-down menu for Data, click "Protected sheets and ranges".
Select the Item on the sidebar that says "Protecting spreadsheet except for editable ranges: ..."
Scroll to the bottom of the list of unprotected ranges.
Click "Change permissions" in the bottom section under the Permissions header.
Change the radio button to select "Show a warning when editing this range" instead of "Restrict who can edit this range".
Click the "Done" button.
Click the "Done" button in the sidebar.
$$$

The only caveat here is that you will receive a warning every time you try to expand/collapse a row group (unless you check the box to disable warnings for 5 minutes, which I assume you do not want to do to avoid the risk of accidentally editing a protected range).

Hope this helps! Let me know if you figure a workaround for when the range is strictly protected.

Short answer

The desired result could be obtained without scripting and this is recommended for cases where number of cells is small, especially if the OP don't know about scripting. To do so,

Add auxiliary columns for each sorting criteria
Then use the SORT() or QUERY() functions.

Extended answer

Google Sheets has some additional functions regarding those available in other spreadsheet applications. Two of them are SORT() and QUERY() which allow to get the desired result without scripting.

As QUERY() return a blank cell for those containing ? as value, the SORT() function will be used.

Original data

Assume that the original data is in A2:D13.

Auxiliary columns

Auxiliary columns will be used for sorting. Only one aggregate function is presented in order to keep the answer short.

Add the following formulas and fill down until the last row of the source data.

D2: =Row(C2)-Row($C$2)+1
E2: =ISERROR(LOOKUP("?",OFFSET($A$2,MATCH(A2,$A$2:$A$13,0)-1,2,COUNTIF($A$2:$A$13,A2))))
F2: =AVERAGEIF($A$2:$A$13,A2,$C$2:$C$13)

If you find helpful having column headers, add them to the row 1.

Key Formula

G2: =ARRAY_CONSTRAIN(SORT(A2:F13,5,TRUE(),6,TRUE(),1,TRUE()),12,3)

ARRAY_CONSTRAIN is included to limit the result to the columns of the source data.

Google-sheets – How to show which column has the first amount and count columns to reach a sum amount

I would suggest using some helper columns for the second part.

This said, for the first part, I propose this formula:

=index($E$2:$P$2,match(false,isblank(E3:P3),0))

For the second part, I propose this formula (note it has dependents on the helper columns)

=datedif(C3,index($Q$2:$AB$2,match(true,Q3:X3>=B3,0)),"M")+1

The helper columns only have running totals (adds up everything up to but excluding the current month).

Datedif finds the difference between two dates, and in the above, will return that difference in months.

Spreadsheet sample

EDIT: Added the formula for running totals for the second part, which works without the helper columns:

=DATEDIF(C3,index($E$2:$P$2,match(true,MMULT((column(E3:P3)<=transpose(column(E3:P3)))*E3:P3,transpose(SIGN(E3:P3)))>=B3,0)),"M")+1

This bit:

MMULT((column(E3:P3)<=transpose(column(E3:P3)))*E3:P3,transpose(SIGN(E3:P3)))

Returns an array containing the running totals, referenced from this website and adapted to a row (the website uses a column of values and explains in fairly good detail how it works).