How to speed up transfer filtered dataset from one to another Google Spreadsheet using AppScript

Question

I've written a short script to copy over certain rows from one sheet to another. The initial sheet is a larger dataset and the second sheet should be a filtered set to create custom graphs on the dataset more easily.

The current code:

function updateChartRows() {
  var sheetOriginal = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
  var sheetChart = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet2");
  var filterValue = "someValue";    
  
  var matches = sheetOriginal.createTextFinder(filterValue);
  var match = null;
  var i = 0;
  
  while ((match = matches.findNext()) != null) {
    var search_range = sheetOriginal.getRange(match.getRowIndex(), 1,1,3);
    var update_range = sheetChart.getRange(i+3,1,1,3);
    update_range.setValues(search_range.getValues());
    i++;
  }
}

The code is rather simple. It goes to the original sheet and does a find on the data based on a keyword. It gets an array of cells matching the keyword.

I then loop over the matches array and copy the values of the entire row of that cell to a new row on the second sheet. We only need the values from the first three columns. (We can make the assumption te new sheet is empty and we start from the third row due to labeling in the first and second row.)

I notice that the code now has an execution time of roughly 30 seconds. Which I find rather slow since the current original dataset is still on the small side. Roughly 60 records.

I assume the slowness lives in the while loop. For every match it has to do a lookup in the original sheet to find the correct rows and values.

Is there a way to improve on this? For example, instead of creating a textFinder is there better way to get all the relevant data, including the actual values instead of references to the cell?

I also tried the findAll() function on the TextFinder, but I still only get references cell matching the keyword. Thus I still need to do a lookup to get the values from the entire row.

I proposed a modified script for reducing the process cost of the script. Could you please confirm it? If that was not the direction you expect, I apologize. — Tanaike
Hey @Tanaike, Thank you for you quality answer. Gonna check out your proposal later today. But on first sight it's exactly what I was looking for. Regarding the mistake "chosen_stock", good catch! I tried to simplify the code for the purpose of the question. So I renamed some variables and wanted to remove the parameters. Guess something went wrong. Will change that in the question. — Stephan Celis

Tanaike Tanaike · Accepted Answer · 2020-09-12T00:44:09

I believe your goal as follows.

You want to retrieve the values from "sheet1".
You want to search the values with filterValue.
You want to put the filtered values to "sheet2".
You want to reduce the process cost of the script.

Modification points:

For example, SpreadsheetApp.getActiveSpreadsheet() can be used as var ss = SpreadsheetApp.getActiveSpreadsheet(). By this, the cost can be reduced at the 1st 2 lines in your script.
As the method for reducing the cost of your script, I would like to propose the following flow.
1. Retrieve the values from "sheet1".
2. Retrieve the filtered values.
3. Put the filtered values to "sheet2".
I thought that the cost can be reduced by processing the filter process in the array.

When above points are reflected to your script, it becomes as follows.

Modified script:

function updateChartRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetOriginal = ss.getSheetByName("sheet1");
  var sheetChart = ss.getSheetByName("sheet2");
  var filterValue = "someValue";
  
  // 1. Retrieve the values from "sheet1".
  var srcValues = sheetOriginal.getDataRange().getValues();
  
  // 2. Retrieve the filtered values.
  var dstValues = srcValues.reduce((ar, e) => {
    if (e.some(f => f === filterValue)) ar.push(e.splice(0, 3));
    return ar;
  }, []);
  
  // 3. Put the filtered values to "sheet2".
  sheetChart.getRange(3, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
}

How to speed up transfer filtered dataset from one to another Google Spreadsheet using AppScript

1 Answers

Modification points:

Modified script:

References: