1. Formula to Convert Google Drive Links to Image URLs
To convert a Google Drive image link to a format usable in the IMAGE() function (based on the latest Google update):
Formula for Single Image:
For a Google Drive link like this:
https://drive.google.com/file/d/1SBtlVyx_meXh7eMgfhCms5Kf24GdDwlR/view?usp=drivesdk
Use this formula to display the image:
=IMAGE("https://lh3.google.com/u/0/d/1SBtlVyx_meXh7eMgfhCms5Kf24GdDwlR")
Formula for Multiple Images:
If you have multiple Google Drive links in column A, use this formula in another column to extract the file ID and generate the image URLs:
=ARRAYFORMULA(IMAGE("https://lh3.google.com/u/0/d/" & REGEXEXTRACT(A:A, "/d/([a-zA-Z0-9_-]+)")))
2. Formula for Google Drive Links Using the uc?id= Format
For links in this format:
https://drive.google.com/uc?id=1ToYQADo8ht_WPyiJuQyk_JQqkO7rbfIc
You can use the following formula to display the image:
Formula for Single Image:
=IMAGE("https://lh3.google.com/u/0/d/1ToYQADo8ht_WPyiJuQyk_JQqkO7rbfIc")
Formula for Multiple Images:
If you have multiple links in column A with the format https://drive.google.com/uc?id=FILE_ID, use this formula:
=ARRAYFORMULA(IMAGE("https://lh3.google.com/u/0/d/" & REGEXEXTRACT(A:A, "id=([a-zA-Z0-9_-]+)")))
3. Conditional Formatting to Highlight Duplicate Values
Formula to Highlight Duplicate Numeric Values:
For checking duplicates only for numeric values in a specific column (e.g., column A):
=AND(ISNUMBER(A1), COUNTIF(A:A, A1) > 1)
This highlights cells containing duplicate numeric values in red.
Formula to Highlight Duplicate Alphanumeric or Text Values:
For checking duplicates regardless of whether the values are numeric, text, or alphanumeric (e.g., in column B):
=COUNTIF(B:B, B1) > 1
This highlights any duplicates (text, numbers, or alphanumeric values) in red.
4. Shortcuts for Navigating to the Last Filled Row
- To jump to the last filled row in a column (if you're within the data range):
- Windows/Linux: Ctrl + Down Arrow
- Mac: Command + Down Arrow
Make sure you start from a cell that has data!
Apps Script for Automatically Jumping to the Last Filled Row:
If you want a custom script to navigate to the last filled row automatically, use this Google Apps Script:
function goToLastFilledRow() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
sheet.setActiveSelection('A' + lastRow); // Adjust 'A' to your preferred column
}
5. Formula to Find the Last Filled Row Number
If you want to find the last filled row number for a specific column (e.g., column A), use this formula:
=MAX(FILTER(ROW(A:A), A:A <> ""))
This will give you the row number of the last non-empty cell in column A.
6. Keyboard Shortcuts Summary
- Ctrl + Down Arrow / Command + Down Arrow: Go to the last filled cell in the current column (if starting within the data).
- Ctrl + End / Command + End: Go to the last cell with data in the entire sheet.
- Name Box: Type the reference (e.g., A100) to jump to any specific cell.