Import data from URL into all sheets in a Google drive Folder-CodePudding

I am trying to import data from a specific URL into all sheets that are in a Google drive folder, then only keep the data that contains the name of the file.

Getting the files from the folder seems to be working but I cannot manage to have SpreasheetApp working (I think this is where the problem comes from). Maybe there is something wrong with how I manage to get ranges.

Here is what I have done so far :

    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 = "myURL";
          var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
          var csvData = Utilities.parseCsv(csvContent);
         
          var activeSpreadSheet = SpreadsheetApp.open(files);
          var sheets = activeSpreadSheet.getSheets();
          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 = sheet.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("Marchand");
        var cell = sheet.getRange(1,2);
        cell.setValue("Image");
        var cell = sheet.getRange(1,3);
        cell.setValue("Titre de l'offre");
        var cell = sheet.getRange(1,4);
        cell.setValue("Prix");
        var cell = sheet.getRange(1,5);
        cell.setValue("Lien");
        var cell = sheet.getRange(1,6);
        cell.setValue("Categorie");
        
           }
          }
         }

CodePudding user response：

Change

  var activeSpreadSheet = SpreadsheetApp.open(files);
  var sheets = activeSpreadSheet.getSheets();

to (spreadsheet has already been defined previously)

var sheets = spreadsheet.getSheets()

and give a value to sheetIndex, for instance

  var sheets = spreadsheet.getSheets();
  var sheetIndex=0
  var sheet = sheets[sheetIndex]