How to clear range instead of sheet in Google script?

Question

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

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

Answer 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).