Google-sheets – Jump to the end of data in all sheets, not just the current one

google sheetsgoogle-apps-scriptkeyboard shortcuts

I have a Google Spreadsheet with 10 sheets. All of them with ~1000 rows.

I need to go to the end on every tab, not just first sheet.

How can I do that?

Imagine 100 sheets (tabs) in one spreadsheet. All 1000 lines or more. Every time I go to new sheet(tab) I have to scroll down to the end of it. What can be done to avoid scrolling and getting me to either last edited place (in every sheet) or end of data range.
I'm using Windows 10 or Windows 8, US standard keyboard.

Best Answer

I needed something like that:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name:"goto_last", functionName:"goto_last"}];
  sheet.addMenu("Scripts", entries);
   myFunction();
};

function goto_last() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();

  var lastrow = mysheet.getLastRow();

  mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1));
};

thank you for the answers!

Related Solutions

Google Sheets – Query Select Where is Not Null vs <>” Not Filtering Properly

the query only grabs the headers.

No, it grabs some rows, too. Scroll down... way, way down. There they are. Here's why you are hitting this.

Some of your columns are completely empty. These are interpreted as string by default. Hence, is not null is always true for these, and you get the whole table.
Since you order by B (ascending by default), the huge number of empty rows ends up on top, and leads you to conclude that the query returns nothing.

Unfortunately, there seem to be no way to declare the type of an empty column. This, combined with the "no nulls in string column" behavior, leads to people being driven crazy by not null.

To keep your sanity, either

make sure every column has at least one nonempty cell, with some value of the type you expect in that column.
or, use the less powerful but more straightforward filter command when it suffices for the task. Note that the output of filter can be fed into a query, to combine the best features of both.

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Best Answer

Related Solutions

Google Sheets – Query Select Where is Not Null vs <>” Not Filtering Properly

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

Related Topic