I'm confused as to why my function is returning blank when trying to loop through multiple when the same doesn't when doing one. This my sheet https://docs.google.com/spreadsheets/d/1D4hhDaQnH--_ZqaGXp7h6HnhTTFYzbM0MeuHsT-ak1w/edit#gid=0 This is my code:
//arrays input
function testCustom(term, startDate,amount, name, soldBy)
{
var results = new Array(term.length);
//loop through every term number in array
for(var f = 0; f < term.length; f++)
{
//loop through term count
for(var i = 0; i < term[f]; i++)
{
//arrray to store all terms
var termTempArray = new Array(term[f]);
//add increment to date
var newDate = new Date();
newDate = startDate;
var thirdDate = new Date();
thirdDate.setMonth(newDate.getMonth()+i);
//create a new array full of payment date info
var payDate = new Array(4);
payDate[0] = name;
payDate[1] = thirdDate;
payDate[2] = amount;
payDate[3] = soldBy;
termTempArray[i] = payDate;
results[f] = termTempArray;
}
}
return results;
}
This code works where as the above returns blank
//arrays input
function frack(term, startDate,amount, name, soldBy)
{
var results = new Array(term);
//loop through every term number in array
//for(var f = 0; f < term.length; f++)
//{
//loop through term count
for(var i = 0; i < term; i++)
{
//add increment to date
var newDate = new Date();
newDate = startDate;
var thirdDate = new Date();
thirdDate.setMonth(newDate.getMonth()+i);
//create a new array full of payment date info
var payDate = new Array(3);
payDate[0] = name;
payDate[1] = thirdDate;
payDate[2] = amount;
payDate[3] = soldBy;
//add array to results
results[i] = payDate;
}
//}
return results;
}
Instead of having to manually use the second bit of code for every row of data I want to have something that iterates through the range of values and I can't seem to get it to work. I'm new to javascript and google apps script, but not to programming at large.
You need to handle 2D arrays correctly. It is perhaps easier to get all data as one array, and call the function like this:
=RepeatByNumberOfTerms(A2:E)
...where each row in A2:E contains one record, such as:
| Terms | Date | Amount | Name | Sold by |
|---|---|---|---|---|
| 6 | 5/12/2022 | 100 | Jill Smith | Dweezil |
| 2 | 5/12/2021 | 20 | Jane Jones | Moon Unit |
You can then use destructuring assignment syntax, like this:
/**
* Repeats name, amount and soldBy, incrementing date by one month each time.
*
* @param {A2:E} data A range where each row contains term, startDate, amount, name, soldBy.
* @customfunction
*/
function RepeatByNumberOfTerms(data) {
'use strict';
if (!Array.isArray(data) || data[0].length !== 5) {
throw new Error('Expected a range with at least one row of five columns.');
}
const result = [];
data.forEach(row => {
const [terms, startDate, amount, name, soldBy] = row;
if (!Number(terms)) {
return;
}
if (!startDate.getMonth) {
throw new Error(`Expected a date, but '${startDate}' is a ${typeof startDate}.`);
}
const startMonth = startDate.getMonth();
for (let i = 0; i < Math.min(1000, terms); i++) {
const date = new Date(startDate);
date.setMonth(startMonth + i);
result.push([name, date, amount, soldBy]);
}
});
return result;
}
This gets the following results:
| A | B | C | D |
|---|---|---|---|
| Jill Smith | 5/12/2022 | 100 | Dweezil |
| Jill Smith | 6/12/2022 | 100 | Dweezil |
| Jill Smith | 7/12/2022 | 100 | Dweezil |
| Jane Jones | 5/12/2021 | 20 | Moon Unit |
| Jane Jones | 6/12/2021 | 20 | Moon Unit |