📝 Overview
This guide explains how to retrieve the latest row (including the row number, values, and record) from a Google Spreadsheet, along with how to identify the next available empty cell.
With the provided formula, you can:
Obtain the last row’s number and values, even if there are blank rows in between.
Retrieve specific data from sheets without row numbers
Identify the next empty row to append new data.
🔍 Retrieve the Last Row Number and Value
1️⃣ Create a new sheet in the spreadsheet where you want the retrieved data to appear.
2️⃣ Use the following formulas to obtain the last row number and its value.
📌 Formula to Retrieve the Last Row Number
=MAX(MATCH(MAX('【Sheet Name】'!●2:●)+1,'【Sheet Name】'!●2:●,1),MATCH("",'【Sheet Name】'!●:●,-1)
📌 Formula to Retrieve the Last Cell Value
=INDEX('【Sheet Name】'!■2:■, MAX(MATCH(MAX('【Sheet Name】'!●2:●)+1,'【Sheet Name】'!●2:●,1),MATCH("",'【Sheet Name】'!●:●,-1)))
🔔 Notes:
Replace【Sheet Name】 with your actual sheet name.
Replace 【●2:●】 and 【■2:■】with appropriate columns:
Example: ●2:●=A2:A (row numbers)
Example: ■2:■=B2:B (cell values)
💡 Example
✔️ Target Sheet for Data Retrieval (Sheet1):
✔️ New Sheet Created for Data Retrieval:
B2 (Row Number)
=MAX(MATCH(MAX('Sheet1'!A2:A)+1,'Sheet1'!A2:A,1),MATCH("",'Sheet1'!A:A,-1))C2 (Last Values)
=INDEX('Sheet1'!B2:B, MAX(MATCH(MAX('Sheet1'!A2:A)+1,'Sheet1'!A2:A,1),MATCH("",'Sheet1'!A:A,-1)))
🔍 Retrieve the Last Value Without Row Numbers
If the sheet lacks row numbers, use this formula to retrieve the last non-empty cell in a specific column.
📌 Formula
=INDEX(FILTER('【Sheet Name】'!●2:●, '【Sheet Name】'!●2:● <> ""), COUNTA(FILTER('【Sheet Name】'!●2:●, '【Sheet Name】'!●2:● <> "")))
🔔 Notes:
Replace 【Sheet Name】 with your sheet’s name.
Replace ●2:● with the target column (e.g., B2:B).
💡 Example
✔️ Target Sheet for Data Retrieval (Sheet6)
✔️ New Sheet Created for Data Retrieval:
A2 (Last Value):
=INDEX(FILTER('Sheet6'!B2:B, 'Sheet6'!B2:B <> ""), COUNTA(FILTER('Sheet6'!B2:B, 'Sheet6'!B2:B <> "")))
🔍 Retrieve the First Empty Row After the Last Value
To find the next empty cell below the last populated row, use this formula:
📌 Formula:
="[Column Letter]" & (MAX(FILTER(ROW('【Sheet Name】'!【●2:●】), '【Sheet Name】'!【●2:●】 <> "")) + 1)
🔔 Notes:
Replace [Column Letter] with the actual column letter (e.g., "A").
Replace 【Sheet Name】 with the target sheet’s name.
Replace 【●2:●】 with the relevant column range (e.g., A2:A).
💡 Example
✔️ Target Sheet for Data Retrieval (Sheet29):
✔️ New Sheet Created for Data Retrieval:
A2 (Next Empty Cell):
="A" & (MAX(FILTER(ROW('Sheet29'!A2:A), 'Sheet29'!A2:A <> "")) + 1)
🚀 How to Use this Data in Yoom
🔹 To Retrieve the Cell Value Only:
In Yoom, select Google Sheets from the Integrate with Apps.
Choose “Retrieve Value.”
Enter the Spreadsheet ID, sheet information, and cell reference where the value is located.
🔹 To Retrieve the Entire Row:
In Yoom, select Google Spreadsheet from the Manage Database.
Choose “Retrieve records.”
In the “Search target column,” specify the column name that contains the search value and set the condition to “value is not empty.”
🎉 And that’s it!
Now you can easily retrieve the last row number, cell values, and records from Google Sheets.
Related Keywords
Related Keywords
Google Spreadsheet, Google, Google Sheets, Last Row, Row Number, Empty Cell, Data Retrieval, Spreadsheet Guide, Record