Yes, you would just need to include something like this in the "if" statement: if (tabs[i].getSheetName() != 'Table of Contents' && tabs[i].getSheetName().indexOf("keyword") != -1) {

@SheetsNinja oh wow, thanks for the fast response! I'll see if I can make it work in my file, if I can, that'll save me so much time..

There are some "benefits" to chips (for example, with the new multi-select functionality, you HAVE to use chips), and I do appreciate the option to use the chip aesthetic, but I would have greater piece of mind if they made them harder to accidentally delete.

Awesome! Glad this was useful!

Awesome to hear! Music really makes everything better, right? Thanks for checking it out!

So if the data is going to be sorted, then you will want to use a different method. 1) is using a single cell hack, with a walkthrough here: kzbin.info/www/bejne/qHfMd6ulhJljba8 Or 2) you can use a scripted method, which is resilient even when the data is sorted: kzbin.info/www/bejne/a4rcpKljhtSoirM

Google added a new permissions flow for any external connections (any images pulled in or external data connections). This is designed to keep people safe, primarily from making a copy of an existing Google Sheet that has nefarious images or data connections. If you are the one that added the photo link, then you can just click "Allow" up top and you'll be fine!

@ right, how do i go about it then so do the same =image(url)=1? the url im using its actually from instagram...

@@itstammyhere Yes, you use the same formula =image("url",1) When you hit enter, you should get prompted to allow access, once you click "Allow Access", the image should display. Occasionally you may have errors trying to display image links in Google Sheets. 1) Some websites block direct linking to their images (or restricted to logged in users), and 2) sometimes what looks like the link to the actual image is actually a link to the site or webpage and won't display then either. A quick test is to paste the link in a new browser window and see what it displayed (if anything).

You would have to export the chart one at a time. It may or may not work in your final application depending upon whether you can stack the charts there like I did in this walkthrough.

Glad this was helpful!

resolved! silly caps . WRote ID instead of Id

The link provided in the description allows you to make a copy of the Google Sheet to your Google Drive. If you are on Google Workspace, you may have an error if custom scripts are blocked. If you sign into your personal Google Account, you can get around the restriction that way.

So glad this was helpful!

Yes, if you select command palette and search for "high-contrast" you can toggle a different style, but for me, it doesn't make it dark mode and doesn't have any additional customization that the add-on includes, which is a variety of different schemes (which highlights the code differently as well), and the ability to hide the file structure to make for a streamlining coding experience. So if all you want is high-contrast, then you can forgo the extension, but if you do much coding, I think the extension is still worth checking out.

Awesome! So glad this was helpful!

So I got into several methods of doing just that in this video. I go into an advanced case at the end to filter multi-select dropdowns in the data as well as from the reference: kzbin.info/www/bejne/q4TFpKGhm9aLZ7s

So having too much data on the source or destination sheet can be an issue. Google Sheets has a technical limit of around 10 million cells, the functional limit really depends on how many formulas you have on your sheet. Also, importrange also has a limit of how much data you can pull between spreadsheets. I haven't nailed this one down exactly, but somewhere in the range of 500,000 characters (so around 500,000 cells if each cell only contained one character). If you have that much data, you may need to segment unrelated data (or any data that doesn't need to be stored together) into different spreadsheets. So for example, what I may do is yearly archive spreadsheets, condense the data on each one into useable extracts and then reference those in my main spreadsheet.

Yes, it's pretty similar to this, I have another video on the basics of that here: kzbin.info/www/bejne/iZnGlaqEnZyqepI

So you can just add a little bit to the sequence formula: =arrayformula(date(2024,sequence(12,1,11),1)) In the sequence formula, if we do sequence(12) it will just generate a list of numbers from 1 to 12. If we want to customize, we can add more parameters, which in sequence is =sequence(numberOfRows,numberOfColumns,startNum,stepSize) So that is why we keep 12, which is number of rows, then add 1 for number of columns, and then add 11 for our starting month. If we did sequence(12,1,1) we would get the exact same result as I have in the video. So then if you wanted to start in June for example, you would do: =arrayformula(date(2024,sequence(12,1,6),1)) And then you could add the skipping to do something like every quarter, just like this: =arrayformula(date(2024,sequence(12,1,10,3),1)) That would give you 12 quarters starting with 10/1/2024.

@@SheetsNinja Thank you very much. I got it. I just had to change the comma to a semicolon. I needed a sequence of 94 months. It looked like this: =TRANSPOSE(ARRAYFORMULA(DATE(2024;SEQUENCE(94;1;11);1)))

@Junior-e6u4l ah, perfect. Yes it is confusing to convert between the different symbols Google Sheets uses in different countries.

I have a video on that here: kzbin.info/www/bejne/q4TFpKGhm9aLZ7s And I develop it a little further and show a different use case with sumifs here: kzbin.info/www/bejne/mWHCnZqClsx1adk

So one or the other of the dates must be off. What I'll usually do in this scenario is update the date formats to YYYY (so 2024 instead of 24), and typically the year is off when it won't pull it in. (for example, I've seen 2124, 1924, etc., and then if your date is actually 10/1/2124, it's not going to show up for 10/1/2024).

I'm assuming you already have the performance data for the employees, I'm just demonstrating how to take performance data and turn it into a good-looking report.

Awesome, so glad this was helpful!

You would have to add another layer of code that checks if the sheet is visible and only list visible tabs. So for example, in the script, it has this line: if (tabs[i].getSheetName() != 'Table of Contents') { You would want to modify that to: if (tabs[i].getSheetName() != 'Table of Contents' && !tabs[i].isSheetHidden()) {

@@SheetsNinja You truly are a Ninja! Thank you!

@@SheetsNinja another question for you Mr. Ninja? I would LOVE when I click the link for the information in that tab to open in the same sheet as the table of contents sheet. So you could just work in one sheet. Does this make sense? You would click a hyperlink for a tab and next to the table of contents the data from that tab open and is visible and editable until you click a different link. I like the hyperlink thing but it takes you to the tab directly and I have 20 or 30 tabs working and I have to scroll all the way back to the table of contents. What do you think? Does this make sense? Thank you for your time.

@@gregorioh5263 So the easiest way to do this if you only need to view the data is to use a simple "filter()" formula tied to a dropdown with the tab names. If the dropdown has the tab names exactly, and the dropdown is in cell D5, then you could do a filter formula something like this: =filter(indirect(D5&"!A2:M"),indirect(D5&"!A2:A")<>"") Assuming your data tabs go from column A to M and your column A is the one you expect to be filled on every row. Otherwise a scripted version is possible, I will add that to my stack of ideas of videos to record.

So glad you found this helpful!

Yes, I have a video on mail merge here using Google Docs as the mail template: kzbin.info/www/bejne/pKG0faKdrcx9qNk

@SheetsNinja wow. I am not sure why such an incredible piece of "how to software" isn't amongst the first in searches. You are good Mr Ninja. I use Google workspace and app script to develop solutions but no where close to your capabilities. I am pleased and hope to meet you someday.

@SheetsNinja can I get your email from your website for personalised conversations?

With the way it's set up now, I would add this piece of code right in each of the email sections (e.g. follow up email & ready to start email) before anything else in that if statement that would look like this: if (data[6] == '') { SpreadsheetApp.getUi().alert("Please enter email and try again."); sheet.getRange(row,7).clearContent(); return; } I add the line to clear the status so that way after they enter the email, they can select that status again and then it will run correctly.

@@SheetsNinja works like a charm. thank you so much. how about if there's another column where another criteria needs to be met?

I don't know if you saw it, but I just dropped a new video on how to do this, you can check it out here: kzbin.info/www/bejne/p5LJgmSZrsSLh5I

Yes, if you change the format to: "ddd M/d" it should display in the format you're looking for

@@SheetsNinja Thanks for response, I changed it to "ddd M/d" but only getting "017 11/17"

Yes, Google can automatically send an email notification, the purpose of demonstrating doing it manually is the customization that you can accomplish that way.

If I'm understanding you correctly, you'll want to copy / paste as values on in the primary column before deleting the others.

Awesome, so glad this was helpful!

Glad it was helpful!

Can you provide a sample of what you're trying to do with this? Maybe a copy of the Google Sheet with just that section? I just want to see what you're doing to see if there is an easy solution or adjustment.

@SheetsNinja Heyy! Thank you for your response. I am glad to say that I have got the solution. =IFERROR(SUM(FILTER(J:J, ISNUMBER(VALUE(E:E)) * REGEXMATCH(TEXT(E:E, "0"), TEXTJOIN("|", TRUE, "\b" & SPLIT(A52, ", ") & "\b")))))

If you're talking about sorting the results, you can sort filter by wrapping it in the sort() function, but in query, you can actually add single or multi-column sorting as well (I will be recording a video dedicated to the query function shortly, so keep an eye out for it!).

@@SheetsNinja No, what I mean is that depending on which approach (filter vs query), the final outcome is not 100% the same. Because Query inherently sorts differently than the filter (with sort) approach. (it's a very minor quirk, that almost never matters) I'll be looking :)

@@SheetsNinja To clarify: I don't think the difference is noticeable with your examples. I only noticed the sorting difference when I made my own solution a few years ago, with a bigger dataset, and more complex formulas. If I recall correctly, at least when the search is blank, one of them places certain entries from the top, while the other places them from the bottom. Not a big deal, but still different.

You can't reset the default size across the entire spreadsheet, but you can do Ctrl+A to select all the cells in a tab and reset the size for all the cells in that tab. However, you CAN change default font and colors like this: kzbin.info/www/bejne/r53Imnh9fdF-esk

Okay awesome! Good job! 👏

e is a placeholder for the data about the edit event that automatically comes from Google. It contains information about what cell was edited (which is why we can do "let range = e.range;" and that contains the value that was entered "let value = range.getValue()". We also get the sheet or tab that the change occurred on through "let source = e.source.getActiveSheet()" So once we have that information, we can then apply our logic (e.g. What column was edited and on what tab) to determine if and what things we are going to do.

@@SheetsNinja got it thanks