Nugroho's blog.: google apps script

Showing posts with label google apps script. Show all posts

Saturday, November 7, 2015

List Files on Certain Google Drive Folder and It's Subfolders

...and display it in html format using div tag with border so the files in subfolders will displayed in box, and files in other subfolder will displayed in other box. If subfolder have subfolder, then files inside that sub-subfolder will displayed inside box inside box, :s :P :D

The Algorithm itself is bit like this previous post. OK, I copy and paste a little bit of,..., ok most of code from it.

All by Google App Script.

I use string variable named text to receive all output.

Basically, after the script is completely executed, the content of variable text is complete html source code.

:)

Here it is

Code.gs

function listFilesNFolders(form) { 
 var text = ''; 
 var lv = 2; //for level depth 
 var home = DriveApp.getFolderById('0BxZS62a5NdNYUGxySmp2QW41OUU'); 
 text+='<br><h'+lv+'>'+home.getName()+'</h'+lv+'>' 
 var NIM = home.getFoldersByName("aa"); 
 if(NIM.hasNext()){ 
  var NIMfold = NIM.next(); 
  text+='<br><h'+lv+'>'+NIMfold.getName()+'</h'+lv+'>'; 
  text = crawl(NIMfold,text,lv); 
 } else{ 
  text+='<br> Anda belum mengupload tugas'; 
 } 
 Logger.getLog();
  return text; 
} 

function crawl(home,text,lv){ 
 lv++; 
 var files=home.getFiles(); 
 text+='<div id="p'+lv+'"style="border: 1px solid black;">'; 
 while (files.hasNext()){ 
  var file=files.next(); 
  text+='<br>'+file.getName();
  } 
 text+='</div><br>'; 
 var folders=home.getFolders(); 
 while (folders.hasNext()){ 
  var folder=folders.next(); 
  text+='<br><div id="p'+lv+'"style="border: 1px solid black;">'; 
  text+='<br><h'+lv+'>'+folder.getName()+'</h'+lv+'>'; 
  text = crawl(folder,text,lv); 
  text+='<br>'; text+='</div>'; 
 } 
 lv--; 
 return text; 
} function 
doGet(e) { 
 return HtmlService.createHtmlOutputFromFile('form.html'); 
}

..
form.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
    input { display:block; margin: 20px; }

    #p1 { position: relative; left: 10px;}
    #p2 { position: relative; left: 20px;}
    #p3 { position: relative; left: 30px;}
    #p4 { position: relative; left: 40px;}
    #p5 { position: relative; left: 50px;}
    #p6 { position: relative; left: 60px;}
    


    h1 { position: relative;margin-left: 10px;font-weight: bold;}
    h2 { position: relative;margin-left: 20px;font-weight: bold;}
    h3 { position: relative;margin-left: 30px;font-weight: bold;}
    h4 { position: relative;margin-left: 40px;font-weight: bold;}
    h5 { position: relative;margin-left: 50px;font-weight: bold;}
    h6 { position: relative;margin-left: 60px;font-weight: bold;}

    </style>
  </head>
  <body>
 
 <form id="myForm">
    <input type="submit" 
    value="OK"        
    onclick=
    "
    this.value='Proses';
    google.script.run.withSuccessHandler(fileUploaded).listFilesNFolders(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>
    
  </body>
</html>

Look at the HTML code, the key for indentation is <px> tag with x = 1,2,3,4,5,6. The bigger the number, the more left margin increased. I modify the <hx> tag too.

The content of text variable after script's completely executed.

<br><h2>Testing</h2><br><h2>aa</h2><div id="p3"style="border: 1px solid black;"><br>nug.f<br>nug.f<br>nug.f<br>nug.so<br>nug.f<br>nug.f<br>nug.f<br>nug.f<br>nug.f<br>nug.f<br>nug.f<br>F-1.xls</div><br><br><div id="p3"style="border: 1px solid black;"><br><h3>Desktop</h3><div id="p4"style="border: 1px solid black;"><br>.DS_Store<br>tiketpulang.pdf<br>tiketberangkat.pdf<br>Gmail - Bukti Pemesanan Internet PT. KAI Persero.pdf<br>Gmail - Bukti Pemesanan Internet PT. KAI Persero (pulang).pdf</div><br><br><div id="p4"style="border: 1px solid black;"><br><h4>freebsdvbox</h4><div id="p5"style="border: 1px solid black;"><br>Screen Shot 2015-01-01 at 7.20.02 PM.png<br>Screen Shot 2015-01-01 at 1.56.36 PM.png<br>Screen Shot 2015-01-01 at 1.36.32 PM.png<br>Screen Shot 2015-01-01 at 1.36.00 PM.png<br>Screen Shot 2015-01-01 at 1.35.46 PM.png<br>Screen Shot 2015-01-01 at 1.33.32 PM.png<br>Screen Shot 2015-01-01 at 1.32.53 PM.png<br>Screen Shot 2014-12-31 at 11.30.41 PM.png<br>Screen Shot 2014-12-31 at 11.30.38 PM.png<br>Screen Shot 2014-12-31 at 11.30.16 PM.png<br>Screen Shot 2014-12-31 at 11.30.03 PM.png<br>Screen Shot 2014-12-31 at 11.29.13 PM.png<br>Screen Shot 2014-12-31 at 11.28.00 PM.png<br>Screen Shot 2014-12-31 at 11.26.33 PM.png<br>Screen Shot 2014-12-31 at 11.26.18 PM.png<br>Screen Shot 2014-12-30 at 8.16.46 AM.png<br>.DS_Store</div><br><br><div id="p5"style="border: 1px solid black;"><br><h5>freebsdss</h5><div id="p6"style="border: 1px solid black;"><br>.DS_Store</div><br><br></div><br></div><br></div><br><div id="p3"style="border: 1px solid black;"><br><h3>freebsdvbox</h3><div id="p4"style="border: 1px solid black;"></div><br><br><div id="p4"style="border: 1px solid black;"><br><h4>freebsdss</h4><div id="p5"style="border: 1px solid black;"></div><br><br></div><br></div><br><div id="p3"style="border: 1px solid black;"><br><h3>tugas momentum</h3><div id="p4"style="border: 1px solid black;"><br>tugastumbukan.zip<br>tugasmomentum.zip<br>tugasfisikarevisiyangsalah.zip<br>Screen Shot 2015-10-05 at 6.57.37 AM.png<br>IMG-20151004-WA0091.jpg<br>attachments.zip<br>20151004_201055.jpg<br>.DS_Store</div><br><br><div id="p4"style="border: 1px solid black;"><br><h4>attachments</h4><div id="p5"style="border: 1px solid black;"><br>20151004_221456.jpg<br>20151004_221113.jpg<br>20151004_221052.jpg<br>20151004_221024.jpg</div><br><br></div><br><div id="p4"style="border: 1px solid black;"><br><h4>tugasfisikarevisiyangsalah</h4><div id="p5"style="border: 1px solid black;"><br>IMG_1494.JPG<br>IMG_1489.JPG<br>IMG-20151005-WA0012.jpg</div><br><br></div><br><div id="p4"style="border: 1px solid black;"><br><h4>tugastumbukan</h4><div id="p5"style="border: 1px solid black;"><br>IMG-20151004-WA0039.jpg<br>IMG-20151004-WA0038.jpg<br>IMG-20151004-WA0037.jpg</div><br><br></div><br><div id="p4"style="border: 1px solid black;"><br><h4>tugasmomentum</h4><div id="p5"style="border: 1px solid black;"><br>IMG_20151004_143049.jpg<br>IMG_20151004_143023.jpg</div><br><br></div><br></div><br><div id="p3"style="border: 1px solid black;"><br><h3>pyTest</h3><div id="p4"style="border: 1px solid black;"><br>surface3d_demo2.py<br>surface3d_demo.py<br>sin3d.py<br>simple_anim.py<br>simple_3danim.py<br>sierpinski.py<br>nummpyArray.pdf<br>nugroho.so<br>nugroho.f90<br>nugroho.f<br>nug.so<br>nug.f<br>lines3d_demo.py<br>lines.mp4<br>image_demo.py<br>im.mp4<br>hellofortran.so<br>hellofortran.f<br>Fortran-and-CModulePython.pdf<br>contourf3d_demo2.py<br>cobamodul.py<br>coba2.py<br>coba1.py<br>basic_example.py<br>basic.py<br>animate_decay.py<br>animasi.py<br>anim.py<br>air3.py<br>air2.py<br>air.py<br>3danim.py<br>.DS_Store</div><br><br><div id="p4"style="border: 1px solid black;"><br><h4>air</h4><div id="p5"style="border: 1px solid black;"><br>air700.mp4<br>air4098f1024bit20int.mp4<br>air4096f1024bit.mp4<br>air2700f512bit.mp4<br>air1700.mp4</div><br><br></div><br></div>

Yeah, it looks like garbage, but it's actual html code and if we open it in web browser, it will looks like this.

Friday, November 6, 2015

Passing String Variable to a Function on Google App Script

I have text a variable with string type, I want to pass it to a function and change the value there just like previous post (using list type)

but, text variable value can't be changed by passing to function like list (or array). I didn't have append or push syntax.

well, we can still use list

but list have comma, :(

so I updated the value by passing it to a function like this

function htmlOutput(form) {
  var text = 'Tadaa...';
  text+='Test'; 

text = dummy(text);
  return text;
}

function dummy(text){
text+='hehehe';
  return text;
}

Monday, October 26, 2015

List Folders and Files Recursively using Google App Script.

I got the problem because using 'recursive' as function name, :)

At least now I know that, :)

The next? Beautifying the result, :D

function listFilesNFolders(form) {
  var list = [];
  var lv = 0;                    //lv for level or depth
  list.push(['tadaa...<br>']);
  list.push(['heheh...']);
  var row = [];
  row.push('<br> Hello World!!! ');
  list.push(row);
  
  var home = DriveApp.getFolderById('0BxZS62a5NdNYUGxySmp2QW41OUU'); 
  list.push('<br>');
  list.push(home.getName());
  crawl(home,list,lv);
  return list;
}

//don't use 'recursive' as function name, it won't work
function crawl(home,list,lv){
  lv++;
  list.push('<br>tadaa...'+lv+'...');

  var files=home.getFiles();  
  while (files.hasNext()){
    var file=files.next();
    list.push('<br>f '+file.getName());
  }
  
  var folders=home.getFolders();
  while (folders.hasNext()){
    var folder=folders.next();
    list.push('<br>d '+folder.getName());
    
    crawl(folder,list,lv);
  }
}

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

Friday, October 23, 2015

Global Variable on Google App Script

I have trouble accessed it within function,

so I pass it to every function,
not so elegant solution, but it works, :) .

In the code below, I have global variable named list.

I have to passed as parameter on function dummy in order to edit its value or it wouldn't affected or have error message or undefined (I experienced both, :) )

function listFilesNFolders(form) {
  var list = [];
  list.push(['tadaa...<br>']);
  list.push(['heheh...']);
  var row = [];
  row.push('<br> Hello World!!! ');
  list.push(row);
  
  var home = DriveApp.getFolderById('0BxZS62a5NdNYUGxySmp2QW41OUU'); 
  
  dummy('<br> test',list);
  return list;
}

function dummy(d,list){
  list.push(d);
}

The form

 <form id="myForm">
     <input type="submit" 
    value="OK"        
    onclick="
    this.value='Proses';
    google.script.run.withSuccessHandler(fileUploaded).listFilesNFolders(this.parentNode);
    return false;
 "
                    >
 </form>

Thursday, October 22, 2015

List Files and Folder in Certain Folder in Google Drive using Google App Script

Here it is.

I used getFolderById() to determine the folder I want to list the files and folders inside. I used home as variable name of the folder

home.getFiles() is used to gets a collection of all files in home.

home.getFolders() is used to gets a collection of all folders in home.

I have list and row as array or tuple or 'list' to accommodate the result. Maybe it's wasting time to have two variable, but I plan to write code to list 'files and folders inside subfolders' recursively. I know maybe it didn't even need that array variable, :). But just in case...

function listFilesNFolders(form) {
  var list = [];
  list.push(['tadaa...<br>']);
  list.push(['heheh...']);
  var row = [];
  row.push('<br> Hello World!!! ');
  list.push(row);
  

  var home = DriveApp.getFolderById('0BxZS62a5NdNYUGxySmp2QW41OUU'); 
  // Log the name of every file in the user's Drive.
  var files = home.getFiles();
  list.push('<br>Files:');
  while (files.hasNext()) {
    var file = files.next();
    list.push('<br>-',file.getName());
    //Logger.log(file.getName());
  }
  // Log the name of every folder in the user's Drive.
  var folders = home.getFolders();
  list.push('<br>Folders:');
  while (folders.hasNext()) {
    var folder = folders.next();
    list.push('<br>-',folder.getName());
    //Logger.log(folder.getName());
  }
  
  
  return list;
}

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

The Form

  <form id="myForm">
    <input type="submit" 
    value="OK"        
    onclick="
    this.value='Proses';
    google.script.run.withSuccessHandler(fileUploaded).listFilesNFolders(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>

The Result

Hold on, back up!

Google App Script is fun, :)

Here's my simple hello world, :)

What I did is create "form", which actually have no form at all, just submit button to activate listFilesNFolders(form) function on script (Code.gs file)

The code will return output 'tadaa...', 'heheh...', and 'Hello World!!!'.

Notice the html formatting, :)

function listFilesNFolders(form) {
  var list = [];
  list.push(['tadaa...<br>']);
  list.push(['heheh...']);
  var row = [];
  row.push('<br>Hello World!!! ');
  list.push(row);
  

  return list;
}

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

 <form id="myForm">
  <input type="submit" 
    value="OK"        
    onclick="
    this.value='Proses';
    google.script.run.withSuccessHandler(fileUploaded).listFilesNFolders(this.parentNode);
    return false;
    "
                    >
  </form>

Wednesday, October 21, 2015

List All Files and Folders into Google Spreadsheet using Google App Script

I updated my last code, so it will list all folders name too, in addition of files name.

It will search the files and folders name in certain folder,

get the name,

the date it created,

the size,

the url address,

thi file id,

description,

and the MIME type (except in folder, I hardcoded it by write string "folder")

 
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);
   // listFilesAndFoldersInFolder(folder)
    list_all_files_and_folders_inside_one_folder_without_subfolders()
    return "File uploaded successfully " + file.getUrl();
    
  } catch (error) {
    
    return error.toString();
  }
  
}

function list_all_files_and_folders_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())
     
    list.push(row);
  }
  var files = folder.getFolders();
  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(),"folder")
     
    list.push(row);
  }
  
   sh.getRange(1,1,list.length,list[0].length).setValues(list);
  
}

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

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

Pages