1

I used this script from another post but I want to modify to clear range instead of the entire sheet. Instead of sheet.clear() , I want to clear A2:D. (while keeping data in Column E and other columns.

function ListNamedFilesandFolders() {
/* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
Updated since DocsList is deprecated  https://ctrlq.org/code/19854-list-files-in-google-drive-folder
*/


  // List all files and sub-folders in a single folder on Google Drive
  // declare the folder name
  var foldername = 'january 2021';

  // declare this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:D");
  // clear any existing contents
  range.clear();
  // append a header row
  //sheet.appendRow(["Folder","Name", "URL", "ID"]);


  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  // Logger.log("THE FOLDER IS "+foldersnext);// DEBUG

  // declare an array to push data into the spreadsheet
  var data = [];

  // list files in this folder
  // myfiles is a File Iterator
  var myfiles = foldersnext.getFiles();

  // Logger.log("FILES IN THIS FOLDER"); DEBUG

  // loop through files in this folder
  while (myfiles.hasNext()) {
    var myfile = myfiles.next();
    var fname = myfile.getName();
    var furl = myfile.getUrl();
    var fid = myfile.getId();
    var ftype = myfile.getMimeType();
    //Logger.log("File Name is "+myfile.getName()); //Logger.log("Date is "+myfile.getLastUpdated()); //Logger.log("Size is "+myfile.getSize());
    //Logger.log("URL is "+myfile.getUrl()); //Logger.log("ID is "+myfile.getId()); //Logger.log("Description is "+myfile.getDescription());
    //Logger.log("File Type is "+myfile.getMimeType());

    // Populate the array for this file
    data = [ 
      foldersnext,
      fname,
      furl,
      fid,
      
      ];
    //Logger.log("data = "+data); //DEBUG
    sheet.appendRow(data);
  } // Completes listing of the files in the named folder

  // Now get the subfolder
  // subfolders is a Folder Iterator
  var subfolders = foldersnext.getFolders();
  //Logger.log("THE SUBFOLDER(S) ARE"); DEBUG HEADING

  // now start a loop on the SubFolder list
  while (subfolders.hasNext()) {
    var subfolderdata = [];
    var mysubfolders = subfolders.next();
    var mysubfolder = mysubfolders.getName();  
    //Logger.log("Subfolder name:"+mysubfolder); //DEBUG

    // Get the files
    // mysubfiles is a File Iterator
    var mysubfiles = mysubfolders.getFiles();
    //Logger.log("FILES IN THIS FOLDER"); //DEBUG HEADING

    // now start a loop on the files in the subfolder
    while (mysubfiles.hasNext()) {
      var smyfile = mysubfiles.next();
      var sfname =  smyfile.getName();
      
      var sfurl =  smyfile.getUrl();
      var sfid =  smyfile.getId();
      
      var sftype =  smyfile.getMimeType();
      //Logger.log("Subfolder is "+foldersnext+"/"+mysubfolder); 
      //Logger.log("File Name is "+ smyfile.getName()); Logger.log("Date is "+ smyfile.getLastUpdated()); Logger.log("Size is "+ smyfile.getSize());
      //Logger.log("URL is "+ smyfile.getUrl()); Logger.log("ID is "+ smyfile.getId()); Logger.log("Description is "+ smyfile.getDescription());Logger.log("File Type is "+ smyfile.getMimeType());
      subfolderdata = [ 
        (foldersnext+"/"+mysubfolder),
        sfname,
        sfurl,
        sfid,
        ];
      //Logger.log("subfolderdata = "+subfolderdata);
      sheet.appendRow(subfolderdata);
    }
  }
}

enter image description here

I added the following

var range = sheet.getRange("A1:D");
// clear any existing contents
range.clear();

But it always clears the range and start showing result from the row that has no data in next columns (E:...)

This is the test sheet:
https://docs.google.com/spreadsheets/d/1otwS0eM0XX52W0-JphAbvzPmPAieoNj-wpmQc_30J4c/edit?usp=sharing

2
  • 1
    I believe you use too much code for a simple task. Do consider the solutions found in this question Feb 19 at 14:27
  • I already found that question and was helpful but it returns gap between the files ID and the sort column like in the image I added just now . Although it practically worked for me but I wonder how to solve this issue so every time I run the code, The files ID retrieved start from A2 ros Feb 19 at 15:51
1

The code in the question "start showing result from the row that has no data in next columns (E:...)" because it uses appendRow, that is the way that it works. In other words, appendRow will look for the last row holding values / formulas and after that row it will insert a new row.

You should change the logic of your script to use setValues(values) instead of appendRow(rowContent).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.