Skip to content

Instantly share code, notes, and snippets.

@kelaspajak

kelaspajak/listing google sheet file

Created February 16, 2020 08:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kelaspajak/51157aadc7c15ef0f76d12b2b439a2d0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kelaspajak/51157aadc7c15ef0f76d12b2b439a2d0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. kelaspajak created this gist Feb 16, 2020.
    231 changes: 231 additions & 0 deletions listing google sheet file
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,231 @@
    /**
    * A special function that runs when the spreadsheet is open, used to add a
    * custom menu to the spreadsheet.
    */
    function onOpen() {
    var spreadsheet = SpreadsheetApp.getActive();
    var menuItems = [
    {name: 'List All', functionName: 'listAll'},
    {name: 'Split', functionName: 'getSplit'},
    {name: 'DeleteNilRow', functionName: 'deleteNilRow'},
    {name: 'Export JSON', functionName: 'exportSheetAsJSON'},
    {name: "Download as CSV", functionName: "saveAsCSV"}
    ];
    spreadsheet.addMenu('Perintah', menuItems);
    }

    /* /**
    * Google Apps Script - List all files & folders in a Google Drive folder, & write into a speadsheet.
    * - Main function 1: List all folders
    * - Main function 2: List all files & folders
    *
    * Hint: Set your folder ID first! You may copy the folder ID from the browser's address field.
    * The folder ID is everything after the 'folders/' portion of the URL.
    *
    * @version 1.0
    * @see https://gist.github.com/mesgarpour
    */

    // TODO: Set folder ID
    var folderId = '15nrya-1Yt_sr9U8cmxF1JSkksEa6DCli';

    // 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
    var file, data, sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
    sheet.appendRow(["P#","Document","File Path","Category","topic","type","Manual?","Audience","Description","Date","Last Updated","MIME Type","Size","ID","url"]);

    // 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 = [
    '=HYPERLINK("' + childFolder.getUrl() + '","' + childFolder.getName().replace(/"/gi,'') + '")', //Sets Title as anchor text
    '', //Blank Column for #P (Priority)
    parentName + "/" + childFolder.getName(),
    '', //Blank Column for Category
    '', //Blank Column for Topic
    '', //Blank Column for Type
    '', //Blank Column for Manual
    '', //Blank Column for Audience
    childFolder.getDescription(), //Get Folder Desc
    childFolder.getDateCreated(), //Get Folder Creation Date
    childFolder.getLastUpdated(), //Get Folder Last Updated Date
    '', //Leave Blank for since we cannot get mime type.TODO: FIX.
    childFolder.getSize(), //Get Size (Will only show for .zip, .pdf, .jpeg, etc.)
    childFolder.getId(),//Get ID - useful for cross reference or future upload.
    childFolder.getUrl()
    ];
    // Write
    sheet.appendRow(data);

    // List files inside the folder
    var files = childFolder.getFiles();
    while (listAll & files.hasNext()) {
    var childFile = files.next();
    data = [
    '=HYPERLINK("' + childFolder.getUrl() + '","' + childFile.getName().replace(/"/gi,'') + '")', //Sets Title as anchor text
    '', //Blank Column for #P (Priority)
    parentName + "/" + childFolder.getName(),
    '', //Blank Column for Category
    '', //Blank Column for Topic
    '', //Blank Column for Type
    '', //Blank Column for Manual
    '', //Blank Column for Audience
    childFile.getDescription(), //Get File Desc
    childFile.getDateCreated(), //Get File Creation Date
    childFile.getLastUpdated(), //Get File Last Update
    childFile.getMimeType().replace('application/vnd.',''), //Get File Type clean it up to be a little more human readable.
    childFile.getSize(), //Get Size (Will only show for .zip, .pdf, .jpeg, etc.)
    childFile.getId(), //Get ID - useful for cross reference or future upload.
    childFile.getUrl()
    ];
    // Write
    sheet.appendRow(data);
    }

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


    //fungsi untuk split topik dan subtopik
    function getSplit() {
    //var range = SpreadsheetApp.getActiveSheet().getRange("j2:j100");
    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var numCols = rows.getNumColumns();
    var range = SpreadsheetApp.getActiveSheet().getRange("c2:c" + numRows);
    range.splitTextToColumns('/');

    var range = SpreadsheetApp.getActiveSheet().getRange("a2:a" + numRows);
    range.splitTextToColumns('.');
    }

    function deleteNilRow(){
    deleteRow(11);
    }

    function deleteRow(col){ // col is the index of the column to check for 0 or empty
    var sh = SpreadsheetApp.getActiveSheet();
    var data = sh.getDataRange().getValues();
    var targetData = new Array();
    for(n=0;n<data.length;++n){
    if(data[n][col]!='' && data[n][col]!=0){ targetData.push(data[n])};
    }
    Logger.log(targetData);
    sh.getDataRange().clear();
    sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
    }

    /* From https://gist.github.com/IronistM/8be09ebd4c5a4a58c63b */

    function exportSheetAsJSON() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var numCols = rows.getNumColumns();
    var values = rows.getValues();

    var output = "";
    output += "{\""+sheet.getName()+"\" : {\n";
    var header = values[0];
    for (var i = 1; i < numRows; i++) {
    if (i > 1) output += " , \n";
    var row = values[i];
    output += "\""+row[0]+"\" : {";
    for (var a = 1;a<numCols;a++){
    if (a > 1) output += " , ";
    output += "\""+header[a]+"\" : \""+row[a]+"\"";
    }
    output += "}";
    //Logger.log(row);
    }
    output += "\n}}";
    Logger.log(output);

    var folder = DriveApp.getFoldersByName("kelaspajak.github.io").next();
    folder.createFile(sheet.getName()+".json", output, MimeType.PLAIN_TEXT);

    };

    function saveAsCSV() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = SpreadsheetApp.getActiveSheet();
    // create a folder from the name of the spreadsheet
    var folder = DriveApp.getFoldersByName("kelaspajak.github.io").next();
    // append ".csv" extension to the sheet name
    fileName = sheet.getName() + ".csv";
    // convert all available sheet data to csv format
    var csvFile = convertRangeToCsvFile_(fileName, sheet);
    // create a file in the Docs List with the given name and the csv data
    var file = folder.createFile(fileName, csvFile);

    }


    function convertRangeToCsvFile_(csvFileName, sheet) {
    // get available data range in the spreadsheet
    var activeRange = sheet.getDataRange();
    try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
    var csv = "";
    for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
    if (data[row][col].toString().indexOf(",") != -1) {
    data[row][col] = "\"" + data[row][col] + "\"";
    }
    }

    // join each row's columns
    // add a carriage return to end of each row, except for the last one
    if (row < data.length-1) {
    csv += data[row].join(",") + "\r\n";
    }
    else {
    csv += data[row];
    }
    }
    csvFile = csv;
    }
    return csvFile;
    }
    catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
    }
    }