0
134
ViewsHow do you update cells based on data validation
I'm creating a budget with Google Sheets and I want to update totals based on the category. I'm using Data Validation to categorize the transactions but how would I create a button that updates all categories based on the transactions using App Scripts. Basically, I'm trying to use SUM to add everything up but only if they are categorized in a specific category. Thanks for any help that's offered.
·
Juan Pablo Isaza
1 answers
Answer question0
Here's an example:
function createCategories() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
const sh1 = ss.getSheetByName('Sheet1');
const r = SpreadsheetApp.newDataValidation().requireValueInRange(sh1.getRange('A2:A22'));
sh.getRange(2,4,20,1).setDataValidation(r);
}
function catTotals() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
const vs = sh.getRange(2,1,sh.getLastRow() - 1,4).getValues();
let sums ={pA:[]};
vs.forEach((r,i) => {
if(!sums.hasOwnProperty(r[3])) {
sums[r[3]]=r[2];
sums.pA.push(r[3]);
} else {
sums[r[3]] += r[2];
}
});
let html = "<style>td,th{border:1px solid black;}</style><table><tr><th>Category</th><th>Total</th></tr>";
sums.pA.forEach(p => {
html += `<tr><td>${p}</td><td>${sums[p]}</td></tr>`
});
html += '</table><br /><input type="button" value="Exit" onClick="google.script.host.close()" />';
SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),'Category Totals')
}
·
Juan Pablo Isaza
Report
Answer question