Top 10 Google Sheets Advanced User Annoyances

Top 10 things in Google Sheets that annoy advanced users.

#google #sheets #advanced

[xasancle]

1️⃣ 00:14 Named Ranges
2️⃣ 02:49 Table Objects
3️⃣ 03:35 Pivot Table Ranges
4️⃣ 07:29 SUMIFS Arrays
5️⃣ 09:41 INDEX MATCH
6️⃣ 11:38 Slow Calc
7️⃣ 14:13 Slow Spreadsheet
8️⃣ 14:43 No Evaluate Function
9️⃣ 17:21 IMPORTRANGE
1️⃣ 0️⃣ 19:19 Array Size (Bytes)
1️⃣ 1️⃣ 20:40 Script Custom Function Value
1️⃣ 2️⃣ 21:52 Script Trigger Custom Time

[edjusted]

re custom time triggers: an easy workaround is just to do an if/then in the functions. e.g. I have daily triggers that I don't want to run on weekends, so if it's Saturday or Sunday, exit the function. Or I also have hourly triggers that check to see if it's during business hours, etc. Still a pain, but relatively easy to work around.

[bruno_veloso]

Omggg, I am feeling trigged with this video. It's giving me anxious. Google sheets devs please look here! Edit: if you try to use Query with more than 1 IMPORTRANGE combined, it doens't allow you to connect multiple gsheets... it must occur one by one. Let alone trying to create a custom array with IMPORTRANGE from multiple gsheets.

[bigbrother]

Agree with many things in your list, esp. the first three and those about script.
I think it's these nuances that push me to write super advanced/complicated formulas, and probably scripting.

[frankcklin]

wow...why google let these kinds of situations happened? they should do somthing!

[willysplayer]

Not being able to use index match in Arrayformula really grinds my gears...

[donluc]

Min 18 - you can assign security at each tab/sheet level in your other file from which you import. Or use an intermediary file which reads only what is allowed to share and you share the entire file.

[elricho]

Great explain, thank's for sharing

[getdavemoore]

Yep, all of this. And fing IMPORTRANGE had me chasing my tail for a couple of months ...

[marcpennings]

Thanks for pointing out that I'm not the complete idiot that I took myself for, when I was trying to get index and match working! 😅

[getdavemoore]

...and toggle conditional formatting on-off. So useful, but so laggy when filtering on the fly

[casianreport]

For the last, the Script Trigger Custom Time, when i write codes that i want to run in a specific custom date/hours, i write inside a code witch day must be allowed to run. And many times, i put triggers to run every day or hourly but inside a script code i program to let the code to run, in a specific day or interval period. Practicaly, i make a script inside function, to check after the code is triggered, the day and hour, and check if is the day or the period, or hour periods that i pre-allow it, if is not, end the trigger by return nothing. Hope this idea will help.

[felipe]

The mass calculation is an issue indeed. Is it possible to do something like this:
[On b1 cell] =counta(A:A)
[On b2 cell] =sum(A:A"&b1&")?
I know this wont work, but you have more knowledge base than me, but i think you got my point .

[sagine]

To remove Access you can just go to the original one and change the permission for the share option and the sheet will lose access, ( it happened to me by confidence, I give the ownership of the sheet to another user and I lost access to all other linked sheet )

[poom_laupat]

I think you could use =indirect to solve the 1st problem.

[micahswerens]

Any plans to do any guides on Google Advanced Sheets Service? I can't seem to find much on it, I'm trying to do faster batch creation/deletion of sheets within a spreadsheet. Just because it works doesn't mean that a script that takes 8 minutes to run is optimal. LOL.

[mohamed.montaser]

the 50 importrange limitation got removed long time ago .. i use over 50 importrange

[octaviolange]

And the problem of not showing the pivot table editor??? 🤯🤯🤯

[logeshmuthu]

Please upload import data from Google sheet to excel

[romandeity]

I hate that the Query function negates hyperlinks