How can I retrieve the value of the last cell that contains data in a spreadsheet using importrange?

edit

You can use the following formula to retrieve the value of the last cell in a spreadsheet using Importrange:

=INDEX(IMPORTRANGE("spreadsheetURL", "sheetname!A1:Z"), MAX(FILTER(ROW(IMPORTRANGE("spreadsheetURL", "sheetname!A:Z")), IMPORTRANGE("spreadsheetURL", "sheetname!"&ROW(IMPORTRANGE("spreadsheetURL", "sheetname!A:Z"))&":Z")<>"")) ,MAX(FILTER(COLUMN(IMPORTRANGE("spreadsheetURL", "sheetname!A:Z")), IMPORTRANGE("spreadsheetURL", "sheetname!"&ROW(IMPORTRANGE("spreadsheetURL", "sheetname!A:Z"))&":"&COLUMN(IMPORTRANGE("spreadsheetURL", "sheetname!Z")))<>"")))

Replace "spreadsheetURL" with the URL of the spreadsheet, and "sheetname" with the name of the sheet you want to retrieve the data from.

This formula uses the INDEX function to look up the value in the last cell that contains data. It first imports the data from the specified sheet using IMPORTRANGE, then uses the MAX and FILTER functions to determine the row and column of the last cell with data. Finally, the INDEX function is used to retrieve the value at the specified row and column.