Find matching value in a file for multiple spreadsheets

Question

I have multiple spreadsheets in a folder, for each spreadsheet and I would like to get the matching data from an external file. For example if my spreadsheet is called 'iphone 7' I would like to get in that sheet all the records from my external file containing 'iphone 7'

Here is how far I got (I am pretty new to scripting !) :

function myfunction()
{ 
 var root = DriveApp.getFoldersByName("Produits");                                        
  while (root.hasNext())
  { 
   var folder = root.next();    //If the folder is available, get files in the folder
   var files = folder.getFiles();                                                                       
   while(files.hasNext())       //For each file,                                                                    
   { 
    var spreadsheet = SpreadsheetApp.open(files.next()); 

    //import data from URL
     var csvUrl = "https://incensy.tempurl.host/test-ct-flux.csv";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
 
  var sheets = spreadsheet.getSheets()
  var sheetIndex=0
  var sheet = sheets[sheetIndex]
   sheet.getRange(2, 1, csvData.length, csvData[0].length).setValues(csvData);

//Only keep data that contains the file name
  var name = spreadsheet.getName();
  let range = sheet.getDataRange(),
      maxRows = sheet.getMaxRows(),
      srchCol_1 = 2,
      srchPatt_1 = new RegExp(name, "i"),
      newRangeVals = range.getValues().filter(r => r[0] && srchPatt_1.exec(r[srchCol_1])),   
      numRows = newRangeVals.length;  
  range.clearContent();
  sheet.getRange(2,1, numRows, newRangeVals[0].length).setValues(newRangeVals);
  console.log('myfunction')
  sheet.deleteRows(numRows + 1, maxRows - numRows);

   }
  }
 }

There is something wrong in the second part of the code I cannot figure out.

Answer 1

In your situation, how about the following modification?

Modified script:

function myfunction() {
  var keywords = ["sample1", "sample2"]; // Please set the keywords you want to filter to the column "C".

  // Retrieve CSV data.
  var csvUrl = "https://incensy.tempurl.host/test-ct-flux.csv";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent, ";");

  // Retrieve Spreadsheet and put the CSV data.
  var root = DriveApp.getFoldersByName("Produits");
  while (root.hasNext()) {
    var folder = root.next();
    var files = folder.getFiles();
    while (files.hasNext()) {
      var spreadsheet = SpreadsheetApp.open(files.next());
      var name = spreadsheet.getName().toUpperCase();
      var values = csvData.reduce((ar, r) => {
        if (!keywords.some(e => r[2].includes(e)) && r.join("").toUpperCase().includes(name)) {
          ar.push(r);
        }
        return ar;
      }, []);
      if (values.length == 0) continue;
      var sheet = spreadsheet.getSheets()[0];
      sheet.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
    }
  }
}

• In this modification, the CSV data is retrieved at the outside of the while loop. Using the retrieved CSV data and each Spreadsheet name, the data is put to the 1st tab of each Spreadsheet.

References:

• reduce()
• toUpperCase()
• includes()