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

@ExcelGoogleSheets

3 жыл бұрын

Thanks!

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.

@ExcelGoogleSheets

3 жыл бұрын

For me the worst part is that you can't disconnect it. But yes, having to click allow every time if you have a bunch of spreadsheets is super annoying. There should be just a separate interface to manage connection to other sheets instead of typing the function to get the prompt.

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.

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.

@lpanebr

3 жыл бұрын

I was just going to say that. But really, all the other issues are truly annoying.

@joangomezvalls6310

3 жыл бұрын

I use a one hour time trigger every day of the week. This script is linked to a sheet and looks in a control cell for the SI value. This cell returns yes or no, depending on the queries you have in the cells. Example: Table of Days from Monday to Sunday and in the right column I put a YES or a NO, depending on how I want the script to be executed that day. It is compared with the function now () and if it matches the day it is YES. Another table: Hours, The table will be from 0 to 24 hours and on the right is a YES or a NO , It is compared with the function now () and if it matches it returns YES. If there are 2 YES, the control cell returns YES and the script runs. In this way, you only have a script and you pass control to the google sheet user. It is not a perfect solution, but it does solve part of the problem. I hope it was helpful or to give an idea to make a video. Greetings.

@ExcelGoogleSheets

3 жыл бұрын

@ Joan Gomez Valls This is likely to cause problems with timezones.

@ExcelGoogleSheets

3 жыл бұрын

@edjusted good approach!

@joangomezvalls6310

3 жыл бұрын

@@ExcelGoogleSheets It may be, but all the time work is done by the sheet, I think that if the sheet is correctly parameterized it should not give an error in time zone issues.

Great explain, thank's for sharing

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

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.

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

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

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! 😅

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.

@ExcelGoogleSheets

Жыл бұрын

"you can assign security at each tab/sheet level in your other file from which you import." How?

@donluc007

Жыл бұрын

@@ExcelGoogleSheets in the menu, go to "Data" > "Protect sheets and ranges"; select tab Sheets, then click "Set permissions"; good luck!

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

@ExcelGoogleSheets

3 жыл бұрын

IMPORTRANGE is one of the functions that you learn when you come from Excel background and then you realize the nightmare that it is :)

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 )

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.

@ExcelGoogleSheets

3 жыл бұрын

Not at the moment. Have you checked my Google Colab videos? Those with threading may get things much faster for you.

@micahswerens2472

3 жыл бұрын

@@ExcelGoogleSheets I have not, I will have to look for those, thanks.

Please upload import data from Google sheet to excel

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 .

@ExcelGoogleSheets

2 жыл бұрын

use INDIRECT function

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

@ExcelGoogleSheets

3 жыл бұрын

how?

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

@ExcelGoogleSheets

3 жыл бұрын

Do you know if there is a new limit now?

@mohamed.montaser

3 жыл бұрын

@@ExcelGoogleSheets i think there is a limit but i'm pretty sure it will go over 100. the other limitation about size last time i checked was 200,000 cells including blank ones

@ExcelGoogleSheets

3 жыл бұрын

I've heard that 200K cell count, but that never worked for me. I always get errors at various cell counts.

I hate that the Query function negates hyperlinks

@ExcelGoogleSheets

3 жыл бұрын

Use FILTER function instead.

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

Please send this to Google to fix it

Nice ...... I'm ASPJ...... Abaidullah Saleem Pakistani Jatt

Nice 👍👍 ..... I'm ASPJ .. Abaidullah Saleem Pakistani Jatt