Summary.
Google Sheets is a powerful tool. Google Sheets with App Script are even more capable and the best part: App Script is not difficult to learn. In this tutorial, we explain the App Script used to feed our cats in our Feed My Cats Savings Dashboard.
Part 1. Creating the Dictionaries.
A dictionary is data type that consists of keys (look up values) and values (the corresponding values) pairs. Another way to think about keys is to think of them as the "words" in dictionary and the values as the "definitions". Unlike an actual dictionary, a dictionary used in coding does not have to be ordered. As long as the key exists in the dictionary, a value will be returned. A value could be anything: numbers, strings, lists, or other data structures.
We used dictionaries in our App Script as a way to related certain cells to the parent fund name dropdown cells (i.e. B11, G11, L11, B40, G40, L40).
// dashboard name
const SHEET_NAME = 'Feed My Cats Dashboard';
// all prices
const prices = {'1':20,'2':40,'3':80,'4':160,'5':320,'6':640};
// all total saved cells
const total_saved = {'E10': 'E15','J10':'J15','O10':'O15',
'E39':'D44','J39':'J44','O39':'O44'};
// all items # cells
const items = {'E10':'E9','J10':'J9','O10':'O9',
'E39':'E38','J39':'J38','O39':'O38'};
// all checkboxes
const boxes = {'E10':'E10','J10':'J10','O10':'O10',
'E39':'E39','J39':'J39','O39':'O39'};
// all available spent
const available = {'E10':'D17','J10':'I17','O10':'N17',
'E39':'D46','J39':'I46','O39':'N46'};
// all dropdown cells
const dropdowns = {'B11':'D17','G11':'I17','L11':'N17',
'B40':'D46','G40':'I46','L40':'N46'};
// all fund names cells
const funds = {'B11':'B11','G11':'G11','L11':'L11',
'B40':'B40','G40':'G40','L40':'L40'};
// all date cells
const dates = {'E10':'D18','J10':'I18','O10':'N18',
'E39':'D47','J39':'I47','O39':'N47'};
// total saved and fund name
const savedAndFund = {'B11': 'E15','G11':'J15','L11':'O15',
'B40':'E44','G40':'J44','L40':'O44'};
// Checkbox and fund name mapping
const boxesAndFunds = {'E10':'B11','J10':'G11','O10':'L11',
'E39':'B40','J39':'G40','O39':'L40'};
Part 2. The Feeding Function.
The Feeding Function performs 2 main actions: (1) Checks if there is enough savings to buy Cat Food and (2) Feeds the cat and logs the action if there is enough savings. We added comments to help explain what each line does to further breakdown the code.
function onEdit(e){
const ss = e.source.getActiveSheet();
const ss2 = e.source.getSheetByName(SHEET_NAME);
const ss3 = e.source.getSheetByName('My Feeding Log');
if (!e)
throw "Do not run onEdit from script editor"
if (ss2.getName() !== SHEET_NAME) return;
// get selected cell
var selectedCell = SpreadsheetApp.getActive().getActiveRange().getA1Notation();
// get cell values
var buyCell = ss2.getRange(selectedCell).getValue();
// Buy Cat Food
if (selectedCell == boxes[selectedCell]) {
var selectedItem = ss2.getRange(items[selectedCell]).getValue().toString();
var curr_saved = +ss2.getRange(total_saved[selectedCell]).getValue();
var availToBuy = +ss2.getRange(available[selectedCell]).getValue();
Logger.log("Buy Decision : " + buyCell);
Logger.log("Total Saved so far : " + curr_saved);
Logger.log("Selected Item No. : " + selectedItem);
Logger.log("Available to Spent on Food : " + availToBuy);
// get price from prices dictionary
var price = +prices[selectedItem];
//var curr_spent = curr_saved - curr_avail;
Logger.log("Cost of Food Item No. "+ selectedItem + " : " + price);
// buy Food and update available to spend
var checkBox = boxes[selectedCell];
var dateCell = dates[selectedCell];
// buy food and log activity
if (buyCell == true && price <= availToBuy) {
// get current date for last date fed
const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
const newDate = Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy");
// gets next empty row on My feeding log
var lastRow = ss3.getLastRow()+ 1;
// append feeding inputs to Feeding Log Sheet
var fundName = ss2.getRange(boxesAndFunds[selectedCell]).getValue();
const columns =['B'+lastRow, 'C'+lastRow, 'D'+lastRow];
ss3.getRange(columns[1]).setValue(fundName);
ss3.getRange(columns[0]).setValue(newDate);
ss3.getRange(columns[2]).setValue(price);
// update last fed date
ss2.getRange(dateCell).setValue(newDate);
var message = 'Cat Food Purchased! Keep it up and feed your other cats.';
ss2.getRange(checkBox).uncheck();
}
else {
var message = 'Not Enought Savings to buy cat food. Save more!';
ss2.getRange(checkBox).uncheck();
}
//return message results
SpreadsheetApp.getUi().alert(message);
}
}
Part 3. The Full Code.
For readability, the full App Script Code is also provided below.
/** @OnlyCurrentDoc */
// dashboard name
const SHEET_NAME = 'Feed My Cats Dashboard';
// all prices
const prices = {'1':20,'2':40,'3':80,'4':160,'5':320,'6':640};
// all total saved cells
const total_saved = {'E10': 'E15','J10':'J15','O10':'O15',
'E39':'D44','J39':'J44','O39':'O44'};
// all items # cells
const items = {'E10':'E9','J10':'J9','O10':'O9',
'E39':'E38','J39':'J38','O39':'O38'};
// all checkboxes
const boxes = {'E10':'E10','J10':'J10','O10':'O10',
'E39':'E39','J39':'J39','O39':'O39'};
// all available spent
const available = {'E10':'D17','J10':'I17','O10':'N17',
'E39':'D46','J39':'I46','O39':'N46'};
// all dropdown cells
const dropdowns = {'B11':'D17','G11':'I17','L11':'N17',
'B40':'D46','G40':'I46','L40':'N46'};
// all fund names cells
const funds = {'B11':'B11','G11':'G11','L11':'L11',
'B40':'B40','G40':'G40','L40':'L40'};
// all date cells
const dates = {'E10':'D18','J10':'I18','O10':'N18',
'E39':'D47','J39':'I47','O39':'N47'};
// total saved and fund name
const savedAndFund = {'B11': 'E15','G11':'J15','L11':'O15',
'B40':'E44','G40':'J44','L40':'O44'};
// Checkbox and fund name mapping
const boxesAndFunds = {'E10':'B11','J10':'G11','O10':'L11',
'E39':'B40','J39':'G40','O39':'L40'};
function onEdit(e){
const ss = e.source.getActiveSheet();
const ss2 = e.source.getSheetByName(SHEET_NAME);
const ss3 = e.source.getSheetByName('My Feeding Log');
if (!e)
throw "Do not run onEdit from script editor"
if (ss2.getName() !== SHEET_NAME) return;
// get selected cell
var selectedCell = SpreadsheetApp.getActive().getActiveRange().getA1Notation();
// get cell values
var buyCell = ss2.getRange(selectedCell).getValue();
// Buy Cat Food
if (selectedCell == boxes[selectedCell]) {
var selectedItem = ss2.getRange(items[selectedCell]).getValue().toString();
var curr_saved = +ss2.getRange(total_saved[selectedCell]).getValue();
var availToBuy = +ss2.getRange(available[selectedCell]).getValue();
Logger.log("Buy Decision : " + buyCell);
Logger.log("Total Saved so far : " + curr_saved);
Logger.log("Selected Item No. : " + selectedItem);
Logger.log("Available to Spent on Food : " + availToBuy);
// get price from prices dictionary
var price = +prices[selectedItem];
//var curr_spent = curr_saved - curr_avail;
Logger.log("Cost of Food Item No. "+ selectedItem + " : " + price);
// buy Food and update available to spend
var checkBox = boxes[selectedCell];
var dateCell = dates[selectedCell];
// buy food and log activity
if (buyCell == true && price <= availToBuy) {
// get current date for last date fed
const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
const newDate = Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy");
// gets next empty row on My feeding log
var lastRow = ss3.getLastRow()+ 1;
// append feeding inputs to Feeding Log Sheet
var fundName = ss2.getRange(boxesAndFunds[selectedCell]).getValue();
const columns =['B'+lastRow, 'C'+lastRow, 'D'+lastRow];
ss3.getRange(columns[1]).setValue(fundName);
ss3.getRange(columns[0]).setValue(newDate);
ss3.getRange(columns[2]).setValue(price);
// update last fed date
ss2.getRange(dateCell).setValue(newDate);
var message = 'Cat Food Purchased! Keep it up and feed your other cats.';
ss2.getRange(checkBox).uncheck();
}
else {
var message = 'Not Enought Savings to buy cat food. Save more!';
ss2.getRange(checkBox).uncheck();
}
//return message results
SpreadsheetApp.getUi().alert(message);
}
}
If you have questions or suggestions, please comment below. In the meantime, Happy Planning!
Comments
Post a Comment