Keeping Last Line of Google Sheet Visible
December 19, 2021 1:40 PM   Subscribe

I have a Google Sheet that has a line added to it automatically from a Withings Sleep Monitor. is there a way to make sure that the last line of the Sheet is always visible. What happens now is the cursor is at the top of the Sheet and I have to scroll down.
posted by 922257033c4a0f3cecdbd819a46d626999d1af4a to Computers & Internet (9 answers total)
Filter view that’s sorted by reverse date?
posted by itesser at 2:05 PM on December 19, 2021

Response by poster: reverse date would seem to be the way to go about it....but the date ( and I can't control this ) is in the form
Month, Day, Year. So sorting then becomes April first or September first.
posted by 922257033c4a0f3cecdbd819a46d626999d1af4a at 2:16 PM on December 19, 2021

Have you tried formatting the date column as Date (Format > Number > Date), and then sorting? it should sort properly then.
posted by telophase at 3:09 PM on December 19, 2021 [1 favorite]

Best answer: I found a way here, but it's a little technical and a little slow:
  1. Go to Extensions > App Script.
  2. Delete the sample code and paste the following:
    function onOpen(){
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheetname = ss.getSheets()[0].getName();
     // Logger.log("DEBUG: sheetname = "+sheetname)
     var sheet = ss.getSheetByName(sheetname);
     var lastRow = sheet.getLastRow();
     var range = sheet.getRange(lastRow,1);
  3. Click the floppy disk icon to save.
  4. Now, close your sheet and reopen. After more time than you think it should take (count to fifteen), it'll jump to the bottom.
I think it's a little faster for later openings, but the first time at least, it'll look like it didn't work for a while. Also, this might not work on the mobile app.
posted by ectabo at 3:50 PM on December 19, 2021

Is there any chance you can modify the sheet to add a time stamp for each row? Then sort. Alternatively, you could possibly use Dynamic Auto Serial Numbering , and then filter by max row.
posted by oceano at 6:23 PM on December 19, 2021

Response by poster: ectabo's suggestion is close. This morning the cursor was still on the line it was left on when I went to bed. But after adding 3 more lines it didn't scroll down to the end. I'll see what it does tomorrow and probably mark that as the best answer, as it does save scrolling 200 lines.

oceano, This is a Sheet populated via an IFTTT recipe.
posted by 922257033c4a0f3cecdbd819a46d626999d1af4a at 6:01 AM on December 20, 2021

Control + Down Arrow will bring you the bottom of a column so you don't have to scroll.

You could try hiding the rows you don't want to see. I usually start hiding at Row 2 and go down until there are about 10-20 visible rows. Just highlight the ones you want and right click, Hide.

If you have a header row, freeze the top row - select a cell in Row 1 and go to View -> Freeze -> Row 1. Generally if you do this in Excel and close the file with the bottom row selected, it will open again in the same spot. Not as sure about Sheets but it is worth a try.
posted by soelo at 6:16 AM on December 20, 2021

You could rewrite the data onto a new sheet so that the most recent is at the top...
Add a new worksheet.
In cell A1, enter the formula
=query('Source Data'!A:H, "Select * order by E desc", 1)

'Source Data' is the worksheet that has the original data
A:H is the range of your source data.
E is the column that contains the dates. (this must be capitalized)
1 is the number of header rows.
posted by marplot at 8:32 AM on December 20, 2021

You could insert a function in another cell or another tab to pull up the last value in a range
posted by watrlily at 6:58 PM on December 20, 2021

« Older Android 12 notification issues   |   Determining proper power supply for legacy digital... Newer »

You are not logged in, either login or create an account to post comments