CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jon Bowles
Jon Bowles

Reputation: 129

Populate Google sheet with file names from folder on Google drive and add checkbox Google Script

Can anyone help me with a script I am currently butchering to import file names into a sheet and add a checkbox to the appended row...

I am trying to get the file names into K7:K with a checkbox next to it in L7:L, Ive been playing around for hours and decided I may as well swallow my pride and ask a silly question on here!

My current code is:

function listFilesInFolder(folderName) {

SpreadsheetApp.getActive().toast("Populating Register...");

    var sheetName = "Register" 
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  

//change the folder ID below to reflect your folder's ID 
var folder = DriveApp.getFolderById("18KVczz-XW_3OSmCsVVIxd8p38maDzNm3");
var contents = folder.getFiles();

var cnt = 0;
var file;

while (contents.hasNext()) {

    var file = contents.next();
    cnt++;

       data = [
            file.getName(),          
        ];
        
        sheet.appendRow(data);

    var checkbox = SpreadsheetApp.newDataValidation().requireCheckbox().build();
    sheet.getRange("L:L").setDataValidation(checkbox).setValue("FALSE");
        
    }
}

Obviously, its pretty far off, if anyone could help me out I'll be forever grateful! :)

Upvotes: 1

Views: 879

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15308

Try this

function listFilesInFolder(folderName) {
  SpreadsheetApp.getActive().toast("Populating Register...");
  var sheetName = "Register" 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var folder = DriveApp.getFolderById("18KVczz-XW_3OSmCsVVIxd8p38maDzNm3");
  var contents = folder.getFiles();
  var cnt = 0;
  var file;
  while (contents.hasNext()) {
      var file = contents.next();
      cnt++;
      sheet.getRange("K"+(6+cnt)).setValue(file.getName());
    }
  var checkbox = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  sheet.getRange("L7:L"+(6+cnt)).setDataValidation(checkbox).setValue("FALSE");
}

If you want to add new data :

function listFilesInFolder(folderName) {
  SpreadsheetApp.getActive().toast("Populating Register...");
  var sheetName = "Register" 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var folder = DriveApp.getFolderById("18KVczz-XW_3OSmCsVVIxd8p38maDzNm3");
  var contents = folder.getFiles();
  var cnt = sheet.getLastRow();
  var file;
  while (contents.hasNext()) {
      var file = contents.next();
      cnt++;
      sheet.getRange("K"+(cnt)).setValue(file.getName());
    }
  var checkbox = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  sheet.getRange("L7:L"+(cnt)).setDataValidation(checkbox).setValue("FALSE");
}

Upvotes: 1

Related Questions