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
list google sheet file, formatting and download as csv or json
Raw
listing google sheet file
/**
* 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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment