Google Script - Setvalues() not fetching the desired result-CodePudding

I am trying to fetch all the files' link (spreadsheet link) added in a specific google drive folder. However, while running the below code, even though I am able to get the desired output in Logger, it's not getting added to the google sheet using setvalues() function. The issue is not any error, but it's returning only one file name and that does not help. Not really sure of a way to crack this, would really appreciate all your help in this!

  
  var folder = DriveApp.getFolderById("FOLDERPATH");

  var filesIterator = folder.getFiles();

  var file;
  var fileType;
  var ssID;
  var data;
  
  while(filesIterator.hasNext()){
    file = filesIterator.next();
    fileType = file.getMimeType();
    if(fileType === "application/vnd.google-apps.spreadsheet"){
      ssID = file.getId();
      data = getDataFromSpreadsheet(ssID);
      Logger.log(ssID);
      
      
    }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Input");
  var range = sheet.getRange(2,1).setValues([[ssID]]);  
  }
  
}

CodePudding user response：

Try:

  var folder = DriveApp.getFolderById("FOLDERPATH");

  var filesIterator = folder.getFiles();

  var file;
  var fileType;
  var ssID;
  var data;
  
  while(filesIterator.hasNext()){
    file = filesIterator.next();
    fileType = file.getMimeType();
    if(fileType === "application/vnd.google-apps.spreadsheet"){
      ssID = file.getId();
      data = getDataFromSpreadsheet(ssID);
      Logger.log(ssID);

      SpreadsheetApp.getActiveSpreadsheet()
                    .getSheetByName("Input")
                    .appendRow(ssID)
                
    }

  }

Because you said your script is running correctly through logging, I'm assuming var range = sheet.getRange(2,1).setValues([[ssID]]); is overwriting the value in the A2 cell repeatedly.

See:

.appendRow(rowContents)

CodePudding user response：

Get all data for all spreadsheets in a folder

function myfunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Input");
  const folder = DriveApp.getFolderById("FOLDERPATH");
  const files = folder.getFiles();
  while (files.hasNext()) {
    file = files.next();
    if (file.getMimeType() == "application/vnd.google-apps.spreadsheet") {
      ssID = file.getId();
      let tss = SpreadsheetApp.openById(ssID);
      sh.getRange(sh.getLastRow()   1,1).setValue(tss.getName());
      tss.getSheets().forEach(s => {
        let vs = s.getDataRange().getValues();
        sh.getRange(sh.getLastRow()   1, 1).setValue(s.getName());
        sh.getRange(sh.getLastRow()   1, 1, vs.length, vs[0].length).setValues(vs);
      });
    }
  }
}