concatenategoogle sheetsworksheet-function
I'm trying to update this formula so it will work inside of an 'arrayformula'.
This works on its own and when I fill it down.
=if(E2>0,CONCATENATE(B2, " | ",C2," | ",D2), "")
This does not work when rows are added to the spreadsheet.
=arrayformula(if(E2:E>0,CONCATENATE(B2, " | ",C2," | ",D2), ""))
It only repeats the values in row 2 over and over again.
How should I write 'b2' and 'c2' and 'd2' so they adjust to row3, 4, 5, and so on as the number of rows expand?
I wrote a custom function in Google Apps Script that takes all the criteria you want it to have (and more) in consideration.
function myPayments(paymentPlan, dateRange) {
var output = [];
for(var i=0, iLen=dateRange.length; i<iLen; i++) {
var checkDate = new Date(dateRange[i]);
var weekDay = checkDate.getDay()+1, sum=0;
for(var j=0, jLen=paymentPlan.length; j<jLen; j++) {
if(paymentPlan[j][1] == weekDay) {
if(paymentPlan[j][2] === "" && paymentPlan[j][3] === "") {
sum += paymentPlan[j][0];
} else if(paymentPlan[j][2] === "") {
if(checkDate <= new Date(paymentPlan[j][3])) {
sum += paymentPlan[j][0];
}
} else if(paymentPlan[j][3] === "") {
if(checkDate >= new Date(paymentPlan[j][2])) {
sum += paymentPlan[j][0];
}
} else if(paymentPlan[j][2] !== "" && paymentPlan[j][3] !== "") {
if(checkDate >= new Date(paymentPlan[j][2]) &&
checkDate <= new Date(paymentPlan[j][3])) {
sum += paymentPlan[j][0];
}
}
}
}
if(sum == 0) {
output.push([]);
} else {
output.push([sum]);
}
}
return output;
}
If you extend the paymentPlan range, to contain the rest of the data as presented in the PAYMENT PLAN sheet, then you will notice that overlapping payments will be summed.
Add the script under Tools>Script editor and press the save button (in your case it's already there).
I've added the solution in your example file.
Once you are running code in the Sheet, you can loop through the column to see if the values you care about are set or not. You can then only send the email if your criteria are met. Keep in mind that if your logic is if (empty > 20) you will get an email on entry 20, 21, 22, etc. You may want to send one every 5 entries over 20 or something like that.
Best Answer
Please try:
I would have expected:
to produce the same results as above, yet it does not. Whereas normally
&rather than CONCATENATE is just a matter of personal preference, it seems they are not full interchangeable. No mention of that at CONCATENATE.However, Google Spreadsheets has CONCAT where "Equivalent to the
&operator" is mentioned. This:So is a substitute for
&... provided for the concatenation of one 'pair' at a time. Hence:does produce the same results 'as above'.
Seems somewhat curious to me, as does M$'s deprecating of CONCATENATE in favour of CONCAT, released in Excel 2016 (though that does allow joining of more than two strings together).