Copy filtered range from one spreadsheet to another - Google app script

Question

I have a large google sheet with 30275 rows and 133 columns in a google sheet. I want to filter the data and copy column AZ to another spreadsheet.

Link to spreadsheet: https://docs.google.com/spreadsheets/d/1aiuHIYzlCM7zO_5oZ0aOCKDwPo06syXhWvhQMKgJE2I/edit?usp=sharing

I have been trying to follow this link

I am not that familiar with javascript and the code is designed to exclude items from filter rather than including items on filter. I have 500+ items to exclude so need to work out something that will be efficient in filtering large dataset in short time before execution limit is reached.

Here is my code so far. Any help to get this working would be appreciated.

NOTE: Filter/ Query with importrange formulas dont work due to the large volume of data. So I need an efficient script to filter large dataset and move them to another sheet before execution time limit.

function filtered() {
  var ss = SpreadsheetApp.openById('1u9z_8J-tvTZaW4adO6kCk7bkWeB0pwPcZQdjBazpExI');
  var sheet = ss.getSheetByName('Sheet1');
  var destsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('JockeyList');
  var demosheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Betting data - Demo');
  var jockey = demosheet.getRange('L14').getValues();
  // Get full (non-filtered) data
  var values = sheet.getRange('A:EC').getValues();

  // Apply filter criteria here
  //Logger.log(jockey);
  var hiddenValues = jockey;
  values = values.filter(function(v) {
    return hiddenValues.indexOf(v[51]) == 1;
  });
  
  Logger.log(values.length);

  // Set filtered data on the target sheet
  destsheet.getRange(10, 5, values.length, 2).setValues(values);
  
}

RayGun RayGun · Accepted Answer · 2020-12-21T04:23:58

Ok so it seems like you want to copy only the values from AZ in 'Sheet1' that are equal to whatever string value is contained in cell L14 of sheet 'Betting data - Demo.' If that is the case, then here is a change to your original code that will accomplish that:

function filtered() {
  var ss = SpreadsheetApp.openById('1u9z_8J-tvTZaW4adO6kCk7bkWeB0pwPcZQdjBazpExI');
  var sheet = ss.getSheetByName('Sheet1');
  // this assumes that your destsheet and demosheet are in the same spreadsheet. Keep in mind that opening spreadsheets with SpreadsheetApp is costly so you want to minimize your calls to new sheets.
  var destsheet = ss.getSheetByName('JockeyList');
  var demosheet = ss.getSheetByName('Betting data - Demo');
  var jockey = demosheet.getRange('L14').getValue();
  var searchTerm = new RegExp(jockey);
  // Get full (non-filtered) data
  var values = sheet.getRange('A:EC').getValues();
  
  // Apply filter criteria here and return ONLY THE VALUE IN COLUMN AZ
  var filteredValues = values.reduce(function(resultArray, row) {
    if (searchTerm.test(row[51])) resultArray.push([row[51]]);
    return resultArray;
  }, [])
  // Set filtered data on the target sheet
  // Note* not clear why you are starting at row 10, but as is this will overwrite all of the data in column 5 of destsheet starting from row 10 every time this function runs
  destsheet.getRange(10, 5, filteredValues.length, 1).setValues(filteredValues);
}

As it says in the code sample, this will only copy and paste the value in column AZ of 'sheet1'. It does not alter 'sheet1' in any way. If that is really all you want to do, then this function works, but it's overkill. Since you are just filtering values in AZ against a single string value, it would be more efficient to simply count the number of times that string occurs in column AZ and then add the string that number of times to your destination sheet.

Also note that your original function is pasting values into destsheet into a constant row (row 10). That means that every time your function runs, the data from row 10 on will be overwritten by whatever data you are copying from 'sheet1'

Copy filtered range from one spreadsheet to another - Google app script

1 Answers