Google sheet : Get values from the web and keep value matching each file in a folder

Question

I am trying to do something and I am almost there but I have been scratching my head on a 'length' issue for quite some time now. What I want to do :

• Get data from an URL (web csv file)
• Loop through all spreadsheets in a folder
• For each spreadsheet only keep the data from the web csv file that contains the spreadsheet's name.

My code works on a single page but not when I try to apply it to all spreadsheets in the folder. I have this error : "TypeError: Cannot read property 'length' of undefined myfunction @ Getproductfiles.gs:35"

Here is the full code :

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);

// Always show 3 decimal points
var cell = sheet.getRange("D2:D");
cell.setNumberFormat("00");

//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)
      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);
  sheet.deleteRows(numRows + 1, maxRows - numRows);

//Clean headers
var cell = sheet.getRange(1,1);
cell.setValue("Column1");
var cell = sheet.getRange(1,2);
cell.setValue("Column2");
var cell = sheet.getRange(1,3);
cell.setValue("Column3");
var cell = sheet.getRange(1,4);
cell.setValue("Column4");
var cell = sheet.getRange(1,5);
cell.setValue("Column5");
var cell = sheet.getRange(1,6);
cell.setValue("Column6");

   }
  }
 }

Answer 1

Try it this way:

function myfunction() {
  var csvUrl = "myurl";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  if (csvData && csvData.length > 0) {
    var root = DriveApp.getFoldersByName("Produits");
    while (root.hasNext()) {
      var folder = root.next();
      var files = folder.getFiles();
      while (files.hasNext()) {
        let file = files.next();
        let ss = SpreadsheetApp.open(file);
        let sh = ss.getSheets()[0];
        sh.getRange(2, 1, csvData.length, csvData[0].length).setValues(csvData);
        sh.getRange("D2:D" + sh.getLastRow()).setNumberFormat("00");
        SpreadsheetApp.flush();
        let name = ss.getName();
        let [h,...vA] = sh.getDataRange().getValues();
        let maxRows = sh.getMaxRows();
        let vs = vA.filter(r => r[0] && r[2] == name);
        let numRows = vs.length;
        sh.getDataRange().clearContent();
        SpreadsheetApp.flush();
        if (vs && vs[0].length) {
          sh.getRange(2, 1, numRows, vs[0].length).setValues(vs);
          sh.deleteRows(numRows + 1, maxRows - numRows);
          sh.getRange(1, 1, 1, 6).setValues([["Column1", "Column2", "Column3", "Column4", "Column5", "Column6"]]);
        }
      }
    }
  }
}