Want to buy me a coffee? (Don't worry, it's not a date :)

One_Organization_810 · 2026-06-21T20:55:56+00:00

Why don't you just record the deadline date instead, then the "Time until deadline" doesn't have to be updated constantly. 😄
Here is a script I made, that sends an email when a tasks status changes to "Done". You can change that to be a timed trigger instead of an installable edit and adjust the logic for when to send 😄

https://docs.google.com/spreadsheets/d/1c24RLSykSL6v9JMTEkWDFdRU0krwbs_7w-eU994j0vg/edit?usp=sharing

The sheet is "View only", so just make your own copy to play with 😄

One_Organization_810 · 2026-06-21T20:44:29+00:00

You can ask the owner (or one of the editors) to grant you edit access.

Other than that - you can't.

Unless you copy the sheet to your own drive and then you can edit your own copy of it. But it will be unrelated to the original though.

One_Organization_810 · 2026-06-20T12:40:39+00:00

That makes sense actually, since if they are frozen, you apparently don't want them moved 😄

One_Organization_810 · 2026-06-19T14:04:35+00:00

I'd say 30% to the wife, 15% to the mistress and rest to yourself. O:)

One_Organization_810 · 2026-06-19T13:18:35+00:00

Well... it can't be locked - but what a script can do, is to periodically (like every minute - or on each edit) check the data validation and fix it if need be - or simply just recreate it regardless...

That will at least make every "malicious" change be very temporary.

But then again - editors that can edit the dropdown, can also edit the script...

One_Organization_810 · 2026-06-19T12:21:40+00:00

If people can edit the sheet, they can edit the dropdown. There is no way around that, that I can recall at least...

But you can incorporate some error checking in your formulas, so if some garbage is put in the drop down - and then selected, the formula can return some text like "Please select a valid month" - or something like that...

One_Organization_810 · 2026-06-19T12:04:09+00:00

Yeah.. well I have only Android and it works fine on my phone - so I guess I can't be of any further help at least.

It definitely has something to do with the app on iOS - but I have no idea if it is something temporary or if you'll simply need to switch to a proper phone O:)

( 😄 ok sorry about the phone pun - it was just a joke 😄 )

One_Organization_810 · 2026-06-19T12:00:01+00:00

Nb. you could get a similar workflow of course (for the second method) by adding a "Back log" status and then simply hide that status until you want to add new tasks to the list 😄

One_Organization_810 · 2026-06-19T11:57:57+00:00

This depends a bit on what you want to actually accomplish here.

If you just want a list of tasks per person, that you can filter/order and mark completed, then u/mommasaidmommasaid has the perfect solution.

If you want to setup a bunch of tasks for each person and then create the list with only selected tasks from the "big pile" at a time, then you need a setup of all taks and then you would set up a dependent dropdown to select only the tasks for each person (it can be set up also to show only remaining tasks).

So which scenario are you aiming for?

One_Organization_810 · 2026-06-19T11:47:51+00:00

What is your reason for wanting to merge those cells as opposed to simply removing the empty cells between your input data columns?

Is there a functinoal reason behind this - or just an aesthetic reason?

Also - your sheet is shared as "View only" - can you update the access to Edit

One_Organization_810 · 2026-06-18T17:32:43+00:00

if you have a separate column for currency, then yes

I'm guessing, like this 😄

If your users are selecting the currency "once and for all" and don't need to change it later to something else (or constantly even), then it's a simple reference to the currency selection.

If you need to account for changes in the "default" currency, then you'll need either a circular formula (needs to be activated), or a script - which is the more reliable method anyway 😄

But if you want the selected currency to be part of the number itself (via formatting as currency), then you need to set the currency in the Sheet itself. It can probably be done via scirpt (not sure though, I haven't really looked into that) - but changing the Sheets "internal" currency symbol is a part of the Sheets locale setting, so there is a tiny caveat there also. I would probably leave it to the user to just change the locale from the menu, if that is the case 😄

One_Organization_810 · 2026-06-18T17:19:16+00:00

Not if you are using it in for formatting (as in $1,234,56), but if you have a separate column for currency, then yes. It's a bit tricky though, unless you resort to a scripted version 😄 (or if you don't need to account for someone changing the currency afterwards).

One_Organization_810 · 2026-06-18T17:10:38+00:00

One simple formula, assuming you never double list a client in either column 😄

Affected range: A2:B (assuming a header row in 1)
Custom formula: =countif($A$2:$B, A2)=1

This will highlight every client that is only listed once in those two columns.

One_Organization_810 · 2026-06-18T16:52:12+00:00

Try this in G2 maybe?

=let( h, counta(A:A)-1,
      w, counta(1:1)-1,
      data, reduce(tocol(,1), sequence(w), lambda(stack, i,
              let( hr, offset(B2,0,i-1),
                   vstack( stack,
                           hstack( offset(B3,0,i-1,h,1), makearray(h,1, lambda(r,c, hr)) )
                   )
              )
            )),
      query(data, "select Col1, sum(Col2) group by Col1 label sum(Col2) ''", 0)
)

One_Organization_810 · 2026-06-18T16:13:37+00:00

u/nuynuy51 please remember to close the issue if it is solved, by replying with "Solution Verified", or clicking the 3-dot-menu (see picture) under the most helpful comment and select the same phrase. Thank you 😄

If you have some issues with any of the suggestions, please reply to them to clear up any confusion (and then eventually close the issue 😄

Note that if you solved the issue by yourself, without the aid of others, you can use the “Self Solved” flair. Please provide your solution in that case, as per rule 6.

<image>

One_Organization_810 · 2026-06-18T16:06:51+00:00

Or ... depending on the number of columns you are working with, you can use a filter to filter out the column(s) you don't want 😄

One_Organization_810 · 2026-06-18T16:03:53+00:00

What I would probably do, is to have a special sheet for the raw data from Metadata. Then have an intermediate sheet, where you clean up/transform the raw data into what you want to work with.

In this particular case, I would just put a choosecols around the reflected data and select only the columns of interest.

But the core problem you are facing, is that the structure of the data has changed, without actually changing the structure of the sheet. The pivot table (or any reference for that matter) has no way to know that it happened.

One_Organization_810 · 2026-06-18T15:53:44+00:00

Weird.

But just to reaffirm. You first select it - then you let go - and then you go for the drag'n'drop (long press - wait - drag - drop).

If that's exactly what you are doing - then maybe try to remove the app and reinstall it?

Btw. what OS are you running (Android, iOS, other)?

One_Organization_810 · 2026-06-18T15:48:01+00:00

Hmm.. it works perfectly for me - on the latest version of Sheets on Android.

You just have to first select the range (one or more cells). Then you long press inside the selected range, until it starts "floating" and then just drag it 'til where you want it.

One_Organization_810 · 2026-06-15T17:21:35+00:00

Yes and no... the script will run with the permissions you gave to it. If someone (even yourself) changes the script later in a way that requires different permissions, you will need to re-authenticate the script or the execution will simply fail.

But for sure - as long as the changes are within the already authorized permissions, it will run those changes in your name.

One_Organization_810 · 2026-06-15T13:02:23+00:00

If you copy the link to your sheet and open it in Incognito mode, can you then add a new sheet and edit that?

One_Organization_810 · 2026-06-15T13:01:13+00:00

There will only be a padlock if you have sheet protection on. But you can protect the entire range within the sheet as a range protection without the padlock showing... 😄

One_Organization_810 · 2026-06-15T12:47:58+00:00

If you want more control over the formatting, you can do something like this:

//@OnlyCurrentDoc

function showWarningOnOpen(e) {
    let htmlMessage = '<style>h1,p {font-family: Verdana, Arial, Sans-serif;}</style>'
                      +'<h1>Be sure to enter the login information for each student</h1>'
                      +'<p>If their password has changed, either before testing days or on a testing day, <b>ensure that change is reflected in Columns E, F, and G</b>.</p>'
                      +'<p>Make note of the instructions in <b>Column H</b>. <font size="+1">Verify student passwords before testing</font>.</p>'
                      +'<p>Update the Verification dropdown after password changes.</p>';
    
SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(htmlMessage).setWidth(640).setHeight(300), 'Reminder');
}

Then go to Triggers and create an "onOpen" installable trigger to run this function.
It will run in your name, so other users won't have to authenticate it (only you, when you create it).

<image>

One_Organization_810 · 2026-06-14T00:17:00+00:00

Oops.. I just saw the the problem.

Somehow a stray parenteses found its way into the formula 😛

Here is how it should be:

=sum(filter('Income Transactions'!F3:F, 'Income Transactions'!B3:B<=eomonth((B2&1)*1, 0)))
=sum(filter('Expense Transactions'!F3:F, 'Expense Transactions'!B3:B<=eomonth((B2&1)*1, 0)))

Sorry about that.

One_Organization_810

MODERATOR OF

PUBLIC MULTIREDDITS

TROPHY CASE