Apps Script output to defined range instead of entire sheet

Question

unexperienced coder here with 1st post. Managed to adapt a script to my liking but get stuck with this range vs sheet issue, I hope someone can help.

It's a script that lists all files & folder in Google Drive by FolderID. The script creates a new Tab for the folder name, or updates the sheet if that tab already exist.

I think the script uses

sheet.clearContent();
sheet.appendRow(data);

To write content to the sheet.

The script effectivly only writes to 4 columns, A:D. I've been trying to limit the output of the script to column A:D, so the rest of the sheet remains untouched when rerunning the script. Currently the entire sheet gets clear.Content(ed) when rerunning the script.

I've been trying things like sheet.getRange(A1:D).clearContent() and defining the range in combination with range.setValues(Data) but I'm not able to get desired results.

this is the script I have that works, please help me to only output in columns A:D without altering or noticing whatever is in other columns

// Create menu button with function items
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Fetch Files')
      .addItem('Files & Folders', 'ListAll')
      .addItem('Folders Only', 'ListFolders')
      .addToUi();
}

var IDCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Setup"); //Set Sheet name which contains Folder ID
var folderId = IDCell.getRange("C2").getValue();  //Set Cell that contains Folder ID

// Main function 1: List all folders, & write into the current sheet.
function ListFolders(){
  getFolderTree(folderId, false);
};

// Main function 2: List all files & folders, & write into the current sheet.
function ListAll(){
  getFolderTree(folderId, true); 
};

// Get Folder Tree
function getFolderTree(folderId, listAll) {
  try {
    // Get folder by id
    var parentFolder = DriveApp.getFolderById(folderId);

    // Initialise the sheet - Create sheet with folder name if there is none
    var ss = SpreadsheetApp.getActive();
    var file, data, sheet = ss.getSheetByName(parentFolder.getName());
      if (sheet) {
    Logger.log("found");
  }
  else {
    sheet = ss.insertSheet(parentFolder.getName());
  }
    // Clear range & set headers
    sheet.clearContent();
    sheet.appendRow(["Full Path", "Name", "URL", "Type"]);

    // Get files and folders
    getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);

  } catch (e) {
    Logger.log(e.toString());
  }
};

// Get the list of files and folders and their metadata in recursive mode
function getChildFolders(parentName, parent, data, sheet, listAll) {
  var childFolders = parent.getFolders();

  // List folders inside the folder
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    // Logger.log("Folder Name: " + childFolder.getName());
    data = [ 
      parentName + "/" + childFolder.getName(),
      childFolder.getName(),
      childFolder.getUrl(),
      ];

    // Write
    sheet.appendRow(data);

    // List files inside the folder
    var files = childFolder.getFiles();
    while (listAll & files.hasNext()) {
      var childFile = files.next();
      // Logger.log("File Name: " + childFile.getName());
      data = [ 
        parentName + "/" + childFolder.getName() + "/" + childFile.getName(),
        childFile.getName(),
        childFile.getUrl(),
        childFile.getMimeType(),
        ];

      // Write
      sheet.appendRow(data);
    }

    // Recursive call of the subfolder
    getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll);  
  }
};

Jack Brown Jack Brown · Accepted Answer · 2018-02-06T18:17:25

The function you are looking for to clear only specific cells is

sheet.getRange("A2:D").clearContent()

When you get range in a1Notation i.e "A1:D1", you need to provide it as a string "A1:D". Similarly, to add data to only a specific cell you would do the following:

sheet.getRange("A1").setValue("Your value here")

As you can note getRange(a1Notation) has a drawback of being not being friendly to dynamic or evolving operation for different lengths of data. Hence, you can use a different variation of the function

getRange(row, column, numRows, numColumns)

Your final code will look something like this:

var sheetData = []   //Global variable (array) that can be accessed by every function
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Fetch Files')
      .addItem('Files & Folders', 'ListAll')
      .addItem('Folders Only', 'ListFolders')
      .addToUi();
}

var IDCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Setup"); //Set Sheet name which contains Folder ID
var folderId = IDCell.getRange("C2").getValue();  //Set Cell that contains Folder ID

// Main function 1: List all folders, & write into the current sheet.
function ListFolders(){
  getFolderTree(folderId, false);
};

// Main function 2: List all files & folders, & write into the current sheet.
function ListAll(){
  getFolderTree(folderId, true); 
};

// Get Folder Tree
function getFolderTree(folderId, listAll) {
  try {
    // Get folder by id
    var parentFolder = DriveApp.getFolderById(folderId);

    // Initialise the sheet - Create sheet with folder name if there is none
    var ss = SpreadsheetApp.getActive();
    var file, data, sheet = ss.getSheetByName(parentFolder.getName());
      if (sheet) {
    Logger.log("found");
        sheet.getRange("A2:D").clearContent()  
         //You only need to clear if the sheet is found

  }
  else {
    sheet = ss.insertSheet(parentFolder.getName());
    sheet.appendRow(["Full Path", "Name", "URL", "Type"]);
    //New Sheet would be empty so no need to clearhere
  }
    // Clear range & set headers


    // Get files and folders
    getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);
    Logger.log(sheetData)
    // You write the data from sheetData once at the end. 
    sheet.getRange(2,1,sheetData.length,4).setValues(sheetData)
  } catch (e) {
    Logger.log(e.toString());
  }
};

// Get the list of files and folders and their metadata in recursive mode
function getChildFolders(parentName, parent, data, sheet, listAll) {
  var childFolders = parent.getFolders();

  // List folders inside the folder
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    // Logger.log("Folder Name: " + childFolder.getName());
    data = [ 
      parentName + "/" + childFolder.getName(),
      childFolder.getName(),
      childFolder.getUrl(),
      "Folder"
      ];

    // Write
    sheetData.push(data)   //Instead of appending row each time
    // you push the data to the global array 

    // List files inside the folder
    var files = childFolder.getFiles();
    while (listAll & files.hasNext()) {
      var childFile = files.next();
      // Logger.log("File Name: " + childFile.getName());
      data = [ 
        parentName + "/" + childFolder.getName() + "/" + childFile.getName(),
        childFile.getName(),
        childFile.getUrl(),
        childFile.getMimeType(),
        ];

      // Write
      sheetData.push(data)
    }

    // Recursive call of the subfolder
    getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll);  
  }
};

Final Notes: Please find the documentation of various concepts used here below
Global Variables
Array.push
Range.setValues()

Apps Script output to defined range instead of entire sheet

1 Answers