How do delete an attached Google App Script file and project from a Google Sheet? I recently wanted to copy a Google Sheet and as I did I noticed it had an attached Google App Script file. Here’s what that dialog window displayed: However, I didn’t want the adjoining scripts with the copy, in fact I wanted to delete all the original scripts from the original spreadsheet as these were no longer needed. ...
How can you apply conditional formatting using a custom formula that contains a relative reference to an adjacent row or column in Google Sheets? If you want to highlight a cell in Google Sheets using conditional formatting based on the condition of a nearby cell you can easily do so by using the Custom Formula feature along with the INDIRECT() formula that contains a relative reference. The INDIRECT(cell_reference, is_A1_notation) formula in Google Sheets contains two parameters with the first parameter being a string representing the specific cell reference either as a named range (i.e. salesTax), or an absolute reference known by the common A1 notation with column letter followed by the row number (i.e. A1). The second parameter to the INDIRECT formula is_A1_notation is by default set to TRUE, therefore, when using a relative reference this parameter needs to be set to FALSE. ...
Can a Google Sheets drop-down list in a cell allow you to do multiple select? Google Sheets doesn’t natively support the ability to select more than one item on a drop-down list in a cell, but there is a way where you can click an item in the drop-down list and have the clicked item populate the active cell. Here’s an example of what this would look like: Create Your Drop Down List First, create your drop-down list of items by selecting the cell, or range of cells, where the drop-down list will be available. ...
How do you make an email address in an Excel spreadsheet clickable? To make an email address clickable in a spreadsheet check the format of the cell is not set to text, if not when entering an email address Excel should automatically detect that it is an email address and add the mailto: hyperlink automatically. If after checking the cell is not set to Text and even after entering a valid email address Excel isn’t converting the entry to a hyperlinked mailto: address you can get Excel to force the cell to be a hyperlink using the HYPERLINK() function. ...
How can you create a conditional format in Google Sheets based on the value from another cell or column regardless of whether that cell is on the same sheet or another? To reference any cell or column in the Custom Formula field in Google Sheets’ conditional formatting section use the INDIRECT() function referencing that cell or adjacent column. Recently I had a simple requirement where I wanted to highlight a range of cells according to the MONTH() of a cell containing a date value. ...
What do you do when you’re running a Google App Script and you encounter the following error in the Execution Log area? W e ' r e s o r r y , a s e r v e r e r r o r o c c u r r e d w h i l e r e a d i n g f r o m s t o r a g e . E r r o r c o d e N O T _ F O U N D . Check the libraries you have attached to your project on the left-hand side and see if they are correctly referencing the versions you need from them to operate your code properly. ...
If you’re working with data in Google Sheets you’ll soon come across a time when you will need to clean and format phone number data entries. To clean phone numbers in Google Sheets using the REGEXEXTRACT() function extract all the different fields according to the phone number entries and then combine them all into the desired format. Here’s an example walking step by step through the process of cleaning the data set first, before then moving on to creating the desired phone number format. ...
How do you get the value from a column that is to the left of a VLOOKUP range? The VLOOKUP function is a powerful function that enables you to capture the value from a range, provided the value is to the right of the lookup range. For example, suppose you have the following data in your spreadsheet that contains the employee ID in column A, the name of the employee in column B and their hire date in column C. You cannot search by column B to return column A using the VLOOKUP function. ...
How do you format a cell that contains text like 1st Jan 2022 into a cell that Google Sheets can recognize as a date cell? There are certain types of date formats that can prevent the automatic import of data into Google Sheets into dates. One recent data type I had to deal with was where the day field was an abbreviated ordinal number (i.e. 1st, 2nd, 3rd, 4th… etc) and the month was an abbreviated month (eg. Jan, Feb, Mar… etc) and even the year was abbreviated too (eg. 2022 = 22, 2023 = 23, etc). ...
How do you sort a range containing dates in Google Sheets? Sorting a data set in columns or rows containing date data in Google Sheets can easily be done by highlighting the range and then clicking on Data > Sort Range > Advanced range sorting options, as shown below: If the Sort Range option is greyed out it means you haven’t selected a range. Check you have a range highlighted as shown above where I’ve selected cells A1 to B4. ...