I have a script that imports data from a google sheet. Before importing the data into the new sheet I would like to filter it. In Column2 are our Cost centers listed and all cost centers starting with '41' should be filtered and imported to the sheet. Right now I'm a little bit blocked in saying filtering by column2 where the string startswith '41'.

  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var employeeActuals = SpreadsheetApp.openById(
    "1yL_0eB9b6CQLOshjPglDA-MnP2HZdLeIrKh4DO-qN0c" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in employeeActuals.
  var employeeActualsSheet = employeeActuals.getSheetByName("Overview")
  var range = employeeActualsSheet.getDataRange();
  var rangeValues = range.getValues();

  var databasis = rangeValues.filter(function(Item){return Item[1] === String().startsWith(["41"]) })

  Logger.log(databasis); ```

CodePudding user response：

Try something like this:

function myfunk() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const ss = SpreadsheetApp.openById(gobj.globals.testsourceid);
  const esh = ss.getSheetByName("Sheet1")
  const range = esh.getDataRange();
  const vs = range.getValues();
  let data = vs.filter(e => e[0].startsWith('17'))

  Logger.log(data);
}

Data:

output:

12:09:45 PM Notice  Execution started
12:09:45 PM Info    [[17,1, 17,2, 17,3]]
12:09:47 PM Notice  Execution completed

CodePudding user response：

The getValues() method returns a two dimensional string so in order to filter out the values, you will also need a loop in order to the filtering properly:

var filterVals = [];
for (let i = 0; i < rangeValues.length; i  ) {
    var values = rangeValues[i].filter(element => element.toString().startsWith('41'));
    filterVals.push(values);
}
let databasis = filterVals.filter(element => element.toString() != "")
Logger.log(databasis);

Reference

• Apps Script Range Class getValues().