Google-sheets – Quick editable view of 100 last rows (eg. in new tab) of large spreadsheet

google sheets

I have large google spreadsheet eg. thousands of rows and it's loading time may be longer than small spreadshet. Also usually I need only to see and work with latest 100 rows.

Is possible to create view, eg. new tab with only last 100 rows from another tab?

Both tabs should be synced for edit and also for adding new rows (So that after adding new row to second tab, it will add row also to first and vice versa).

Example of source:

Name  | Value
-------------
First | 1
Sec   | 2
...
PrevL | 5000
Last  | 5001

Example of new sub-view:

Name  | Value
-------------
Xyz   | 4901
...
PrevL | 5000
Last  | 5001

Best Answer

The following piece of code will create an editable 'view'.

Code

var ss = SpreadsheetApp.getActiveSpreadsheet();
var v = 'VIEW', d = 'DATA', sRow = 100, sCol = 26;

function onOpen() {
  SpreadsheetApp.getUi().createMenu("View")
    .addItem("Create view", "createView")
    .addItem("Update source", "updateSource")
    .addToUi();
}

function createView() { 
  var view = ss.insertSheet(v), data = ss.getSheetByName(d), width = data.getMaxColumns();
  view.deleteRows(sRow, (view.getMaxRows() - sRow));
  width < sCol ? view.deleteColumns(width, (sCol - width)) : view.insertColumnsAfter(sCol, (width - sCol));
  return setData(v, d);
}

function updateSource() {
  setData(d, v);
  ss.deleteSheet(ss.getSheetByName(v));
}  

function setData(source1, source2) { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  return ss.getSheetByName(source1).getRange('1:100').setValues(
    ss.getSheetByName(source2).getRange('1:100').getValues()
  );
}

Explained

  1. First a menu item is created ("View"), with two options.
  2. The createView function inserts a new tab with only 100 rows and the same amount of columns the source tab has.
  3. The updateView function re-writes the data to the source range (only 100 rows of course) and deletes the view tab.

Example

I've created an example file for you: Quick Edit View
Either make a copy of the example sheet or paste the code in your script editor (Tools > Script editor...) and press the authentificatie button to authenticate the script.

Perhaps it is wise to create a copy before you go berserk....

Related Topic