I have a code that pulls all my spreadsheet files from different folders on Google Drive and lists them in a spreadsheet. I want this code to also write the data in cell U7 in every file that is written in the spreadsheet. I tried this

var files = fold.getFilesByType(MimeType.GOOGLE_SHEETS); var comentario = files.SpreadsheetApp.getSheetByName("FICHA").getRange("U7").getValue();

(see var comentario) but when I run it I get an error on line 12, typeerror: "cannot read property "0" of undefined. (see [1] and [2] attached below) I have run the code without the var comentario and in that case it runs smoothly and I don't get any errors, so I don't really understand what is happening.

If anybody knows how I can modify my code in order to have it write U7 for every file in the Comentarios column, that would be great. Thank you so much in advance!

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var carpetasSheet = ss.getSheetByName("carpetas");
  var lista_carpetas = carpetasSheet.getRange("A2:A"   carpetasSheet.getLastRow()).getValues();
  var lista_carpetas_ok = lista_carpetas.filter(([a]) => a);
  var sheet = ss.getSheetByName("data");
  sheet.clear();
  ss.appendRow(["Parent Folder","Folder", "Name", "Update","URL", "Comentarios"]);
  

 for (var i = 0; i < 5; i  ) {
    console.log(lista_carpetas_ok[i][0]) // Here, you can see the folder ID in the log.
    var folderid = lista_carpetas_ok[i][0];
  try {
    var parentFolder =DriveApp.getFolderById(folderid);
    listFiles(parentFolder,parentFolder.getName())
    listSubFolders(parentFolder,parentFolder.getName());
  } catch (e) {
    Logger.log(e.toString());
  }
}
function listSubFolders(parentFolder,parent) {
  var childFolders = parentFolder.getFolders();
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    Logger.log("Fold : "   childFolder.getName());
    listFiles(childFolder,parent)
    listSubFolders(childFolder,parent   "|"   childFolder.getName());
  }
}

function listFiles(fold,parent){
  var data = [];
  var files = fold.getFilesByType(MimeType.GOOGLE_SHEETS);
  var comentario = files.SpreadsheetApp.getSheetByName("FICHA").getRange("U7").getValue();
  while (files.hasNext()) {
    var file = files.next();
    data = [ 
      
      parent,
      fold.getName(),
      file.getName(),
      file.getLastUpdated(),
      file.getUrl(),
      comentario,
      ];
    ss.appendRow(data);
  }
}
}```


  [1]: https://i.stack.imgur.com/3ksfP.png
  [2]: https://i.stack.imgur.com/wDO8B.png

CodePudding user response：

Answer for question 1

Your 1st question is as follows.

but when I run it I get an error on line 12, typeerror: "cannot read property "0" of undefined. (see [1] and [2] attached below) I have run the code without the var comentario and in that case it runs smoothly and I don't get any errors, so I don't really understand what is happening.

From your script, I thought that your issue of cannot read property "0" of undefined. might be due to for (var i = 0; i < 5; i ). Because for example, when the length of the array of lista_carpetas_ok is less than 5, such an error occurs. For example, when the length of lista_carpetas_ok is more than 5, no error occurs.

If this issue is removed, how about the following modification?

From:

for (var i = 0; i < 5; i  ) {

To:

for (var i = 0; i < lista_carpetas_ok.length; i  ) {

Answer for question 2

Your 2nd question is as follows.

If anybody knows how I can modify my code in order to have it write U7 for every file in the Comentarios column, that would be great.

From your script, I thought that in the function listFiles, this line of var comentario = files.SpreadsheetApp.getSheetByName("FICHA").getRange("U7").getValue(); is required to be modified. Because in your script, files is the fileIterator. So in this case, an error occurs. When this error is modified, it becomes as follows.

From:

var files = fold.getFilesByType(MimeType.GOOGLE_SHEETS);
var comentario = files.SpreadsheetApp.getSheetByName("FICHA").getRange("U7").getValue();
while (files.hasNext()) {
  var file = files.next();

To:

var files = fold.getFilesByType(MimeType.GOOGLE_SHEETS);
while (files.hasNext()) {
  var file = files.next();
  var comentario = SpreadsheetApp.open(file).getSheetByName("FICHA").getRange("U7").getValue();

Reference:

• getFilesByType(mimeType)