Wednesday, October 21, 2015

Working with Google Script

 I heard it's powerful,
 it could be integrated with Google Drive,
 so I decided to give it a try.

 Try to make a form Name, NIM, Offering, and a button to upload a file.

 Basically, it's super simple form.

 It will upload file to my Google Drive, place it in folder NIM (or create it if there's no folder match with NIM).

 As addition, I created spreadsheet manually and update its content, the list of files in the folders. (still got a trouble)
 
function uploadFiles(form) {
  
  try {
    
    var dropbox = "Testing";
    var folders = DriveApp.getFoldersByName(dropbox);
    
    if (folders.hasNext()) {
      var folder = folders.next();
    } else {
      var folder = DriveApp.createFolder(dropbox);
    }
    
    folders = folder.getFoldersByName(form.myNIM);
    if(folders.hasNext()){
      var anak = folders.next();
    } else{
      var anak = folder.createFolder(form.myNIM);
    }
    
    
    var blob = form.myFile;    
    var file = anak.createFile(blob);    
    file.setDescription("Uploaded by " + form.myName);
 
    
    listFilesInFolder(folder)
    
        
    return "File uploaded successfully " + file.getUrl();
    
  } catch (error) {
    
    return error.toString();
  }
  
}

function listFilesInFolder(folderName) {
 
  //var ssNew = SpreadsheetApp.create("Rekap");
  //https://drive.google.com/open?id=11AzGyCcWfvcE_mUltyjAx17wJDghOS-YH5e_zrHT-zc
  //https://docs.google.com/spreadsheets/d/11AzGyCcWfvcE_mUltyjAx17wJDghOS-YH5e_zrHT-zc/edit#gid=0
  //var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc1234567/edit');
  var ss = SpreadsheetApp.openById("11AzGyCcWfvcE_mUltyjAx17wJDghOS-YH5e_zrHT-zc");
 
  SpreadsheetApp.setActiveSpreadsheet(ss);
  
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  /*
  var myNewSheet = activeSpreadsheet.getSheetByName("Rekap");

  if (myNewSheet != null) {
        activeSpreadsheet.deleteSheet(myNewSheet);
  }

  myNewSheet = activeSpreadsheet.insertSheet();
  myNewSheet.setName("Rekap");
    
  */
  //0BxZS62a5NdNYUGxySmp2QW41OUU
  //var folder = DriveApp.getFoldersByName(folderName).next();
  var id = '0BxZS62a5NdNYUGxySmp2QW41OUU';
  var folder = DriveApp.getFolderById(id);
  var contents = folder.getFiles();
  
  var file, data, sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  
  sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Type"]);
  //sheet.appendRow(["Tadaa..."])
  for (var i = 0; i < contents.length; i++) {
 
    file = contents[i];
    
    if (file.getFileType() == "SPREADSHEET") {
      continue;
    }
        
    data = [ 
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
      file.getUrl(),
      "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
      file.getDescription(),
      file.getFileType().toString()
    ];
    
    sheet.appendRow(data);
  
  }
  
};

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('form.html');
}



The Forms
< form id="myForm">
    Nama <input type="text" name="myName" placeholder="Nama">
    NIM <input type="text" name="myNIM" placeholder="NIM">
    Offering <input type="text" name="myOff" placeholder="Offering">
    
    Upload File<input type="file" name="myFile">
    <input type="submit" value="Upload File" 
           onclick="this.value='Uploading..';
                    google.script.run.withSuccessHandler(fileUploaded)
                    .uploadFiles(this.parentNode);
                    return false;">
  
</form>

<div id="output"></div>

<script>
    function fileUploaded(status) {
        document.getElementById('myForm').style.display = 'none';
        document.getElementById('output').innerHTML = status;
    }
</script>

<style>
 input { display:block; margin: 20px; }
</style>



.

DriveApp don't have getFileType() so I used file.getMimeType() instead, :)

 

function list_all_files_inside_one_folder_without_subfolders(){
  var ss = SpreadsheetApp.openById("11AzGyCcWfvcE_mUltyjAx17wJDghOS-YH5e_zrHT-zc");
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sh = SpreadsheetApp.getActiveSheet();
  sh.clear();
  var folder = DriveApp.getFolderById('0BxZS62a5NdNYUGxySmp2QW41OUU'); // I change the folder ID  here 
  var list = [];
  //list.push(['Name','ID','Size']);
  list.push(["Name", "Date", "Size", "URL", "Download", "Description", "MIME"]);

  var files = folder.getFiles();
  while (files.hasNext()){
    file = files.next();
    var row = []
    //row.push(file.getName(),file.getId(),file.getSize());
    row.push(file.getName(),
             file.getDateCreated(),
             file.getSize(),
             file.getUrl(),
            "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
      file.getDescription(),file.getMimeType())
      //file.getFileType().toString())

    list.push(row);
  }
   sh.getRange(1,1,list.length,list[0].length).setValues(list);
}