How can I filter the data retrieved from UrlFetchApp in Apps Script (Google sheets)-CodePudding

I have a large amount of data coming from an csv file which is stored somewhere online. Instead of adding all the data in a sheet and filtering it there, I would like to filter before I add the data to the sheet. In the script.

Option 1: only keep the rows which contain "X", "Y" or "Z" Option 2: only keep the rows where the category (column) is "X", "Y" or "Z"

My code:

const csvUrl_6 = 'https://pf.tradetracker.net/?aid=428944&encoding=utf-8&type=csv&fid=819281&categoryType=2&additionalType=2&csvDelimiter=;&csvEnclosure="&filter_extended=1';
const delimiter_6 = ';';
const googleSheetId_6 = '1qbLcVMw6xOElVWoVDRksRZ_crVn_8d3uSsjlBhjlQm0';
const targetSheet_6 = 'Test';

function myFunction() {

  let response = UrlFetchApp.fetch(csvUrl_6);
  let data = response.getContentText();

  let pattern = new RegExp(delimiter_6   '(?=(?:(?:[^"]*"){2})*[^"]*$)');
  let rows = data.split(/\r?\n/);
  rows = rows.map((row) =>
    row.split(pattern).map((cell) => cell.replace(/^"(.*)"$/, '$1'))
  );
  if (!rows || !rows.length || rows.length < 1 || !Array.isArray(rows)) {
    throw new Error('Something went wrong!');
  }

  let endingColumn = columnToLetter(rows[0].length);
  let range = `${targetSheet_6}!A1:${endingColumn}${rows.length}`;
  let valueRange = Sheets.newValueRange();
  valueRange.values = rows;
  Sheets.Spreadsheets.Values.clear({}, googleSheetId_6, `${targetSheet_6}!A:ZZ`);
  Sheets.Spreadsheets.Values.update(valueRange, googleSheetId_6, range, {
    valueInputOption: 'USER_ENTERED',
  });

CodePudding user response：

Option 1

Try to change the line:

valueRange.values = rows;

with:

let word = 'Bed & Toebehoren'; // the word you want to filter by
valueRange.values = [rows[0], ...rows.filter(x => x.join('\t').indexOf(word) > -1)];

It will keep only the lines that contain the given word.

Option 2

Change the same line with:

var col = 3; // price column
var value = 27;
valueRange.values = [rows[0], ...rows.filter(x => x[col] == value)];

It will keep only the lines where the value in column 4 (price) is equal 27.

Option 2

You can add more conditions this way:

const conditions = x => 
  x[3] == 27 ||
  x[3] == 30 ||
  x[3] == 180;

valueRange.values = [rows[0], ...rows.filter(x => conditions(x))];