Skip to content

Instantly share code, notes, and snippets.

@risha700
Last active November 10, 2019 18:37
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save risha700/0a9c229ceb701201491594b794ff9dfb to your computer and use it in GitHub Desktop.
Save risha700/0a9c229ceb701201491594b794ff9dfb to your computer and use it in GitHub Desktop.
Google sheet script
//helpers
function flush(){
SpreadsheetApp.flush();
}
function get_cuurent_sheet_name() {
var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
return key;
}
function back_to_dashboard(){
var dashboard = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard")
SpreadsheetApp.setActiveSheet(dashboard)
}
//code
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);
DriveApp.createFile(sheet.getName()+".json", output, MimeType.PLAIN_TEXT);
};
function list_total_sales(){
var output=new Array()
extract_desired_sheets().forEach(function(sheet) {
output.push(sheet.getRange("K7").getValue())
});
return output
}
function auto_create_sheet(){
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var lotto_to_copy="E2:E67"
var lotto_to_paste="D2:D67"
var ranges_to_clear=["J11:O27", "N2:N4", "K2:K4","K6"]
var setZero="F2:F67"
//always set active sheet to last
// todo: auto cronjob triggers events at 11 pm everyday
SpreadsheetApp.setActiveSheet(sheets[sheets.length-1])
//var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var tab_name = Utilities.formatDate(new Date(),"GMT-6", "MMM-dd-yy");
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.duplicateActiveSheet();
ss.renameActiveSheet(tab_name);
var sheet = ss.getSheetByName(tab_name);
sheet.getRange(lotto_to_copy).copyTo(sheet.getRange(lotto_to_paste))
sheet.getRange(lotto_to_copy).clearContent()
sheet.getRange(setZero).setValue(0)
for ( var i=0; i<ranges_to_clear.length; i++ )
sheet.getRange(ranges_to_clear[i]).clearContent()
force_update_dashboard()
}
function cleanup_for_exxon(){
var excludes=["Dashboard","Utils","Nov-09-19"]
var readyArray=new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++)
if (excludes.indexOf(sheets[i].getName())<=-1)
//console.log(sheets[i].getName())
//readyArray.push(sheets[i])
//return readyArray
SpreadsheetApp.getActive().deleteSheet(sheets[i]);
}
function list_total_expense(){
var output = new Array()
extract_desired_sheets().forEach(function(sheet) {
output.push(sheet.getRange("O31").getValue())
});
return output
}
function list_total_cash(){
var output = new Array()
extract_desired_sheets().forEach(function(sheet) {
output.push(sheet.getRange("N2").getValue())
});
return output
}
function list_sheet_names(){
var output = new Array()
extract_desired_sheets().forEach(function(sheet) {
output.push(sheet.getName())
});
return output
}
function extract_desired_sheets(){
var excludes=["Dashboard","Utils","Guide"]
var readyArray=new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++)
if (excludes.indexOf(sheets[i].getName())<=-1)
readyArray.push(sheets[i])
return readyArray
}
function force_update_dashboard(){
var dashboard = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard")
var dict={
"B7":"=list_sheet_names()",
"C7":"=list_total_sales()",
"D7":"=list_total_expense()",
"E7":"=list_total_cash()"
}
Object.keys(dict).forEach(function(cell){
dashboard.getRange(cell).clear()
//13sec extra to give chance for sheet to pickup on change
flush();
dashboard.getRange(cell).setValue(dict[cell])
})
}
//events
function onOpen(e) {
// Add a custom menu to the spreadsheet.
SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
.createMenu("⚙ Super Menu ⚙ " )
.addItem('⚡Create Daily Template', 'auto_create_sheet')
.addItem('⨠ Dashboard', 'back_to_dashboard')
.addSeparator()
.addItem('⟳ Refresh', 'force_update_dashboard')
.addToUi();
flush();
}
function onEdit(){
flush();
}
//trigers
function createTimeDrivenTriggers() {
// Trigger every Day 1 CST AM to create a new sheet with ending inventory
ScriptApp.newTrigger('auto_create_sheet')
.timeBased()
.everyHours(24)
.create();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment