Code to reduce execution time by array / caching??? Google App Script

Question

I'm using the following code to compile a list of hastags which have been seperated from a list of twitter tweets.

This code ignores all rows where the hashtag cell has no value or an error value.

function CopyProcessedHashtags() {

  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = sSheet.getSheetByName("SortedIntoRows");
  var targetSheet = sSheet.getSheetByName("HashtagsList");
  var lastRow = sourceSheet.getLastRow();
  
  for (var i = 3; i <= lastRow; i++) {
    var cell = sourceSheet.getRange("E" + i);
    var val = cell.getValue();
      if (val != "") {
      if (val != "#VALUE!") {
      if (val != "#REF!") {

        var sourceRange = sourceSheet.getRange("B" + i + ":" + "E" + i);    
        var targetRow = targetSheet.getLastRow();
        targetSheet.insertRowAfter(targetRow+1);
        var targetRange = targetSheet.getRange("B" + (targetRow+1));
        sourceRange.copyTo(targetRange, {contentsOnly: true});
  }}}}}
};

The trouble is that there is sometimes thousands of rows and execution time is well over 6 mins.

I know batching can reduce execution time...

EXAMPLE

for (var i = 1; i <= 100; i++) {
  SpreadsheetApp.getActiveSheet().deleteRow(i);

to

SpreadsheetApp.getActiveSheet().deleteRows(i, 100);

... I just don't know how to rewrite my code to work in this manner.

Still very new to google app script.

If somebody could give me a nudge in the right direction you'r help would be VERY much appreciated.

Thanks!!

Answer 1

You can use getValues() to retrieve all data within the range and filter it using Array.filter().

Example:

Code:

function CopyProcessedHashtags() {

  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = sSheet.getSheetByName("SortedIntoRows");
  var targetSheet = sSheet.getSheetByName("HashtagsList");
  
  var data = sourceSheet.getDataRange().getValues();
  var filteredData = data.filter(dataRow => (dataRow[4] != "#VALUE!" && dataRow[4] != "#REF!" && dataRow[4] != ""));
  targetSheet.getRange(1,1, filteredData.length, filteredData[0].length).setValues(filteredData);
}

Sample Data:

Output:

References:

• getValues()
• setValues()
• Array.filter()