5 Ways to Use App Script in Your Google Sheets Dashboards

 


Introduction:

App Scripts is Google's scripting language that can be use to automate and integrate various Google services such as Google Sheets, Gmail, Google Docs, Google Drive, etc.

In this article, we share 5 ways in which we have incorporated App Scripts in our Coffee Theme Budget Dashboard to automate various functions including changing currency, clearing checkboxes, etc. There will also be sample code provided and we'll explain what the code accomplishes so you can write your own App Script code!

In Summary, here are 5 ways in which you can also use App Script code in your own Google Sheets!

  • Method 1 - Changing the currency on multiple sheets.
  • Method 2 - Changing the color of multiples cells.
  • Method 3 - Automatically Importing data from CSV file.
  • Method 4 - Moving data between sheets.
  • Method 5 - Creating a Custom UI Menu.
Please note that there are multiple ways you can write App Script code to achieve the 5 functionality above. The purpose of this article is not to cover all possible codes. Instead, our goal is to share App Script code we wrote for our Dashboards and how it can provide a better user experience. As such, feel free to borrow and experiment with our sample codes!

Method 1 - Changing the currency on multiple sheets. 

Changing the currency in Google Sheets requires you to go to Format > Number > Currency or Currency Rounded or Custom currency.

Luckily, there is a more seamless way to change the currency with App Script, especially if you need to change the currency symbol for multiple cells and sheets. First, create a currency dropdown like below.
Figure 2. Currency Dropdown.

Then, open up the in-browser code editor by going to Extensions > Apps Script. Create a dictionary with currency and currency symbol key-value pair. You can include as many currencies as you wish, just make sure the length of your dictionary matches the length of the currency dropdown. This dictionary will be used to look up the symbol given a particular currency. 
// currencies
const CURS = {
usd: '$',
real: 'R$',
euro: '€',
yuan: '¥',
'pound sterling': '£',
yen: '¥',
'australian dollar': '$',
'canadian dollar': '$',
'indian rupee': '₹',
'baht': '฿',
'won': '₩',
};


Depending on whether you need to change the currency for a single cell, a row (i.e A3:G3), or an entire column (i.e. A3:A10), the App Script code is slightly different but similar.

For a single cell, use this code bellow. Replace K4 with the cell you want to change the currency for. This code will grab the currency from the currency dropdown, look up the symbol from the dictionary, & then apply the symbol to the formatting.
  ss3.getRange('K4').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);

For a row, use this code bellow. Replace 37 with the starting number of the row range. Replace 36 with the number of cells in the row range. For example, if the row range is A2:A:30, then you would replace 37 with 2 and replace 36 with 29.
  ss.getRange(37, WANTS.toUpperCase().charCodeAt() - 64, 36)
.setNumberFormats(new Array(36).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));

For a column, use this code bellow. Replace E54:E60 with the column range you want to change the currency for.
ss3.getRange('E54:E60').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);

Here is the entire code snippet for changing all currency cells and ranges in our Coffee Budget Dashboard.

function onEdit(e){

if (!e)
throw "Do not run onEdit from script editor"

const ss = e.source.getActiveSheet();
const ss2 = e.source.getSheetByName("All Transactions");
const ss3 = e.source.getSheetByName("Monthly Dashboard");
const r = e.range;
var range = ss.getRange(1, 1, ss.getMaxRows(), ss.getMaxColumns());
var range2 = ss2.getRange(1, 1, ss2.getMaxRows(), ss2.getMaxColumns());
var range3 = ss3.getRange(1, 1, ss3.getMaxRows(), ss3.getMaxColumns());
// Changing Currrency
if (r.getA1Notation() == CUR_CELL.toUpperCase()) {
//ave. monthly income formatting
ss.getRange('G3:L3').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);
ss.getRange(37, TOTALS.toUpperCase().charCodeAt() - 64, 48)
.setNumberFormats(new Array(48).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));
ss.getRange(37, WANTS.toUpperCase().charCodeAt() - 64, 36)
.setNumberFormats(new Array(36).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));
ss.getRange(37, NEEDS_SAVINGS.toUpperCase().charCodeAt() - 64, 36)
.setNumberFormats(new Array(36).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));
ss3.getRange('E54:E60').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);
ss3.getRange(58, MD_WANTS.toUpperCase().charCodeAt() - 64, 36)
.setNumberFormats(new Array(36).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));
ss3.getRange(58, MD_NEEDS_SAVINGS.toUpperCase().charCodeAt() - 64, 36)
.setNumberFormats(new Array(36).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));
ss3.getRange(64, DAILY_SPEND.toUpperCase().charCodeAt() - 64, 31)
.setNumberFormats(new Array(31).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));
//monthly dashboard limit formattings
ss3.getRange('K4').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);
ss3.getRange('O4').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);
ss3.getRange('S4').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);
ss3.getRange('C34').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);
ss3.getRange('D34').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);
ss3.getRange('S56').setNumberFormat(`${CURS[e.value.toLowerCase()]}#,###.00`);

let len2 = ss2.getMaxRows()-4;
Logger.log(len2);
//transaction formatting
ss2.getRange(4, TRANS_COLUMN.toUpperCase().charCodeAt() - 64, len2)
.setNumberFormats(new Array(len2).fill().map(_ => [`${CURS[e.value.toLowerCase()]}#,###.00`]));
}}


Method 2 - Changing the color of multiple cells.

Currently if you want to change the color of a cell in Google Sheets you need to select the cell, then go to the paint bucket, and either select one of the pre-set colors or enter a hex code. If you need to change the color for an entire range, then you would need to select the entire range and do the same. 

This process is fairly easy if you only need to do it once. However, for multiple cells or ranges, this process become painstaking tedious and exhausting. Luckily, we can use App Script to automate this process down to change a dropdown. 

First, create a dropdown in your Google Sheets just like the currency dropdown. 

Then, create a dictionary with your color names and hex codes.
const MODES = {
'orange' : '#e7b284',
'autumn red' : '#f3d9d6',
'pastel yellow' : '#fefdf2',
'forest green' : '#bfc4af',
'cream paradise' : '#ead1cb',
'taupe':'#c3b6ab',
'vintage brown':'#c8a07e',
'primrose':'#dacbc5',
'dark ivory':'white'
};

Replace B7:E11 with the cell or range you want to change the color for. The spreadsheet will grab the color selected in the dropdown, look up the corresponding Hex code, and then apply that Hex code. 
ss.getRange("B7:E11").setBackground(mode);

Here is the entire code snippet for changing all background colors in our Coffee Budget Dashboard.

// annual dashboard
ss.getRange("B4").setBackground(mode);
ss.getRange("B7:E11").setBackground(mode);
ss.getRange("C14:E16").setBackground(mode);
ss.getRange("B24:L29").setBackground(mode);
ss.getRange("B32:L32").setBackground(mode);
ss.getRange("B35:L85").setBackground(mode);
// set budget goals
ss.getRange("B19:E21").setBackground(mode);
// annual review
ss.getRange("G6:G13").setBackground(mode);
ss.getRange("G13").setFontColor(mode);
ss.getRange("H14:H21").setBackground(mode);
ss.getRange("H21").setFontColor(mode);
ss.getRange("I6:I13").setBackground(mode);
ss.getRange("I13").setFontColor(mode);
ss.getRange("J14:J21").setBackground(mode);
ss.getRange("J21").setFontColor(mode);
ss.getRange("K6:K13").setBackground(mode);
ss.getRange("K13").setFontColor(mode);
ss.getRange("L14:L21").setBackground(mode);
ss.getRange("L21").setFontColor(mode);


// monthly dashboard
ss3.getRange("C4").setBackground(mode);
ss3.getRange("C7").setBackground(mode);
ss3.getRange("C12").setBackground(mode);
ss3.getRange("C16").setBackground(mode);
ss3.getRange("C21").setBackground(mode);
ss3.getRange("B42:E50").setBackground(mode);
ss3.getRange("G10:S50").setBackground(mode);
ss3.getRange("B66:S105").setBackground(mode);
ss3.getRange("B63:S63").setBackground(mode);

//bill calendar
ss3.getRange("O56").setBackground(mode);
ss3.getRange("S56").setBackground(mode);
ss3.getRange("Q56").setBackground(mode);
ss3.getRange("B54:K54").setBackground(mode);
ss3.getRange("B56:K56").setBackground(mode);
ss3.getRange("B58:K58").setBackground(mode);
ss3.getRange("B60:K60").setBackground(mode);

// calendar lines
ss3.getRange("G9").setBackground("#f8f5f4");
ss3.getRange("G16").setBackground("#f8f5f4");
ss3.getRange("G23").setBackground("#f8f5f4");
ss3.getRange("G30").setBackground("#f8f5f4");
ss3.getRange("G37").setBackground("#f8f5f4");
ss3.getRange("G44").setBackground("#f8f5f4");


Method 3: Automatically Importing Data From CSV File.

We have another article that goes into a deep dive of how this function works, so we'll spare you the redundancy here. Instead, we'll go straight into the code. The code consists of 1 main function and 4 helper functions. The main function will call the 4 helper functions to help it import data. 

Let's explain the main function first. The main function will call a function to prompt the user to enter the name of the CSV file, call a function to locate the file from Google Drive, parse the file, call a function to import the data into Google Sheets, and lastly call a function to display a message that tells the user the import has completed. 
function importCSVFromDrive() {
var fileName = promptUserForInput("Please enter the name of the CSV file to import from Google 
   Drive:");
var files = findFilesInDrive(fileName);
if(files.length === 0) {
displayToastAlert("No files with name \"" + fileName + "\" were found in Google Drive.");
return;
} else if(files.length > 1) {
displayToastAlert("Multiple files with name " + fileName +" were found. Change name and retry.");
return;
}
var file = files[0];
var contents = Utilities.parseCsv(file.getBlob().getDataAsString().replace(/[ \t]+$/gm, '',/,,/g,
   "null"))
var contents = contents.splice(1,contents.length-1);
var sheetName = writeDataToSheet(contents);
displayToastAlert("The CSV file was successfully imported into " + sheetName + ".");
Logger.log(contents)
}


This is first helper function that displays a custom pop-up box, where the user can enter the name of the CVS file. 
        
   Figure 4. Pop-up to enter CSV file name


//Helper function that Prompts the user for input and returns their response
function promptUserForInput(promptText) {
var ui = SpreadsheetApp.getUi();
var prompt = ui.prompt(promptText);
var response = prompt.getResponseText();
return response;
}

This is the second helper function that finds the file. 
//Helper function to returns files in Google Drive that have a certain name.
function findFilesInDrive(filename) {
var files = DriveApp.getFilesByName(filename);
var result = [];
while(files.hasNext())
result.push(files.next());
return result;
}

This is the third function that does the most important work: writing data from the file to Google Sheets.
//Helper function to insert data to new sheet and write the 2D array into it
function writeDataToSheet(data) {
var ss = SpreadsheetApp.getActive();
var transactionSS = ss.getSheetByName('All Transactions');
//gets next empty row
var lastRow = transactionSS.getLastRow()+ 1;
transactionSS.getRange(lastRow, 2, data.length, data[0].length).setValues(data);
return transactionSS.getName();
}

This is the fourth and final helper function for displaying a message after the import is complete. 
//Displays an alert as a Toast message
function displayToastAlert(message) {
SpreadsheetApp.getActive().toast(message, "⚠️ Alert");
}


Here is the code in its entirely for reference.
//Displays an alert as a Toast message
function displayToastAlert(message) {
SpreadsheetApp.getActive().toast(message, "⚠️ Alert");
}

//Helper function that Prompts the user for input and returns their response
function promptUserForInput(promptText) {
var ui = SpreadsheetApp.getUi();
var prompt = ui.prompt(promptText);
var response = prompt.getResponseText();
return response;
}

//Helper function to insert data to new sheet and write the 2D array into it
function writeDataToSheet(data) {
var ss = SpreadsheetApp.getActive();
var transactionSS = ss.getSheetByName('All Transactions');
//gets next empty row
var lastRow = transactionSS.getLastRow()+ 1;
transactionSS.getRange(lastRow, 2, data.length, data[0].length).setValues(data);
return transactionSS.getName();
}

//Helper function to returns files in Google Drive that have a certain name.
function findFilesInDrive(filename) {
var files = DriveApp.getFilesByName(filename);
var result = [];
while(files.hasNext())
result.push(files.next());
return result;
}

function importCSVFromDrive() {
var fileName = promptUserForInput("Please enter the name of the CSV file to import from 
   Google Drive:");
var files = findFilesInDrive(fileName);
if(files.length === 0) {
displayToastAlert("No files with name \"" + fileName + "\" were found in Google Drive.");
return;
} else if(files.length > 1) {
displayToastAlert("Multiple files with name " + fileName +" were found. Change name and retry.");
return;
}
var file = files[0];
var contents = Utilities.parseCsv(file.getBlob().getDataAsString().replace(/[ \t]+$/gm, '',/,,/g,
   "null"))
var contents = contents.splice(1,contents.length-1);
var sheetName = writeDataToSheet(contents);
displayToastAlert("The CSV file was successfully imported into " + sheetName + ".");
Logger.log(contents)
}


Method 4: Moving data between Sheets.

              
                                                 Figure 5. Use Coffee to add Transactions.

Our Coffee Budget Dashboard also allows users to enter transaction information on either the Monthly or Annual Dashboard and click on an image to add it to the Transaction Tab. 

The function grabs the input from the specified cells, locates the last record entered on the Transaction Tab, adds the details to the next empty row on Transaction Tab, and then clears the input. 

function addTransaction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var transactionSS = ss.getSheetByName('All Transactions');

// gets next empty row
var lastRow = transactionSS.getLastRow()+ 1;
// transaction sheet related columns
const columns =['B', 'C', 'D', 'E', 'F'];

// get text from input cells
var transactionInfo =[ss.getRange('C7'),ss.getRange('C8'),ss.getRange('C9'),ss.getRange('C10'),
  ss.getRange('C11')];
// append last row to column and enter values to expenseSS
for (var counter = 0; counter <= 4; counter = counter + 1) {
columns[counter]= columns[counter]+lastRow;
var input = transactionInfo[counter].getValue();
transactionSS.getRange(columns[counter]).setValue(input);
Logger.log(input);
//clear expense cells
transactionInfo[counter].clearContent();
    }
}


Method 5: Creating a Custom UI Menu.

This last function is the easiest to grasp out of all the ones presented in this article. A custom UI menu allows you to create a dropdown menu at the top of Google Sheets as if it was a native menu. You can add anything functions to this custom UI menu. 

 
 Figure 6. Custom UI Menu.

In our case, we added the Automatic Import function. 

function onOpen() {
//custome menu
SpreadsheetApp.getUi()
.createMenu("⊹ ࣪ ˖ Add Transactions ⊹ ࣪ ˖")
.addItem("Import from Drive", "importCSVFromDrive")
.addToUi();
}


If you have question or suggestion, please comment below. In the meantime, Happy Planning!



Comments

Post a Comment