How to use JavaScript Date object to find corresponding data for a particular month in Apps Script
So I am currently making an expense tracker, and there is a feature that I'd like to implement.
So basically:
In this spreadsheet, the first two dates are automatically generated by the JavaScript Date object, while the rest aren't because I wanted to give an example of what my feature would do.
So currently, I have a custom function in Apps Script, one that returns how much a person has spent on a particular brand, like this (this is an HTML sidebar):
And returns this (although the numbers might be a bit off since I changed some of the data):
function perCentBrand(categ){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
var total = 0;
var sum = 0;
values.forEach(function(row){
total+=row[1];
if (row[6]==categ){sum+=row[1]}
})
var val = "You spent a total of " + sum + " on " + categ + " out of " + total + " ." + " Additionally, " + (sum/total)*100 + "%" + " of your income has been spent on " + categ;
var ui = SpreadsheetApp.getUi();
ui.alert(val)
}
The custom function is run in the HTML file like this:
<script>
function runFunc(){
var brand = document.getElementById("brand").value;
google.script.run.perCentBrand(brand);
}
</script>
and:
<form onsubmit="runFunc()">
Is there any way I can implement a similar feature, where for example, if I input the number 1 (corresponding to January), in an HTML input, it would return to me in UI alert the date, Amount Spent, and the Clothing brand?
I am new to JS and Apps Script but here is what I've tried:
function dateChecker(monthVal){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
let date = new Date();
let monthVal = date.getMonth();
values.forEach(function(row){
total+=row[1];
if (row[6] && row[1] && row[5] == monthVal){sum+=row[1]}
})
var val = "In the month of" + monthVal + "here is what you have spent" + row[6] + row[1] + row[5];
var ui = SpreadsheetApp.getUi();
ui.alert(val)
}
Probably, basically you can just add one more argument month
in your function:
function perCentBrand(categ, month) {
...
And one more condition row[0].getMonth() + 1 == month
:
if (row[6] == categ && row[0].getMonth() + 1 == month) { sum += row[1] }
It will give you the sum for a given month and brand. It supposes that year is a current year always.
And of course it's need to add the input area with ID="month" in your form as well and use it in the script about this way:
<script>
function runFunc(){
var brand = document.getElementById("brand").value;
var month = document.getElementById("month").value;
google.script.run.perCentBrand(brand, month);
}
</script>
I don't know how your HTLM-code looks like so it's up to you to add the input area to somewhere.
Date.getMonth()
returns you a number of the month from a date object, where 0 is January, 1 is February, etc.