0

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
1

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....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.