英文:
Custom function to loop through a 2D array used as a calendar - Google App Script
问题
我正在尝试创建一个自定义函数,该函数会查找最后执行的活动,并返回序列中的下一个活动。示例数据,例如,“Chess”之后是“Pilates”,然后是“Elevate”。然后下一个活动是“Kundalini”,依此类推。一旦完成最后一个活动,循环将重新开始。
基本上,要遍历一个2D数组,该数组是一个日历,找到最后(最近)的活动,然后从alt_activity范围返回下一个值。如果上一个活动是alt_activity范围的最后一个值,则从alt_activity范围的开头重新开始。
/**
* 查找日历中最后匹配值的备选锻炼。
* @param {array} input 要搜索的日历数组。
* @return 从@param的最后一行(WEEK)和列(DAY)开始的第一个匹配项。
* @customfunction
*/
function calender_search(calendar_range, alt_activities_range)
{
//按ROW(),COLUMN()搜索,类似于电子表格中的ADDRESS函数
//行代表日历中的周
//let week = Object.keys(calendar_range);
for (week = calendar_range.length - 1; i >= 0; i--)
{
for (day = calendar_range[week].length - 1; j >= 0; j--)
{
//从alt_activities_range中查找匹配项。如果返回-1(无匹配项),则继续循环
IF (alt_activities_range.indexOf(calendar_range[week][day]) > -1)
{
IF (calendar_range[week][day].indexOf(alt_activities_range) = alt_activties_range.length - 1)
{
return alt_activities_range[0];
}
//转到匹配项的索引加1,以返回下一个值
return calendar_range[week][day].indexOf(alt_activities_range) + 1;
}
}
}
}
表1
列A | 列B | 列C | 列D | 列E | 列F | 列G | 列H |
---|---|---|---|---|---|---|---|
锻炼备选项 | Chess | Pilates | Elevate | Kundalini | Scrabble | Gardening | Darning |
Column I |
---|
Crossword |
表2
列A | 列B | 列C | 列D | 列E | 列F | 列G |
---|---|---|---|---|---|---|
DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 | DAY 6 | |
WEEK 1 | Chess | |||||
WEEK 2 | Pilates | Elevate | ||||
WEEK 3 | Kundalini | Scrabble | Gardening | |||
WEEK 4 | Darning | Crossword | Chess | Pilates |
已创建自定义函数,但结果与我根据拼凑的信息所期望的不同。
英文:
I'm trying to create a custom function that finds the last activity performed and returns the next activity in thesequence. Sample data, e.g. "Chess" is followed in by "Pilates" and then "Elevate". Then next activity is "Kundalini", etc. Once, the last activity is done, the cycle repeats.
Essentially, loop through a 2D array that's a calendar, find the last (most recent) activity and then return the next value from the alt_activity range. If the previous activity is the last value from the alt_activity range, start from the start of the alt_activity range once more.
/**
* Looks for the alternate workout in the LAST matching value within the calendar.
* @param {array} input The calendar array to be searched.
* @return The first match starting from the last row (WEEK) and column (DAY) of the @param.
* @customfunction
*/
function calender_search(calendar_range, alt_activities_range)
{
//Search by ROW(), COLUMN() think of ADDRESS function in spreadsheets
//rows are weeks within the calendar
//let week = Object.keys(calendar_range);
for (week = calendar_range.length - 1; i >= 0; i--)
{
for (day = calendar_range[week].length - 1; j >= 0; j--)
{
// look for the match from the alt_activities_range. IF -1 returned (no match) continue loop
IF (alt_activities_range.indexOf(calendar_range[week][day]) > -1)
{
IF (calendar_range[week][day].indexOf(alt_activities_range) = alt_activties_range.length - 1)
{
return alt_activities_range[0];
}
//Go to index of match PLUS 1 for NEXT value to be returned
return calendar_range[week][day].indexOf(alt_activities_range) + 1;
}
}
}
}
Table 1
Column A | Column B | Column C | Column D | Column E | Column F | Column G | Column H |
---|---|---|---|---|---|---|---|
Wrkout Alternatives | Chess | Pilates | Elevate | Kundalini | Scrabble | Gardening | Darning |
Column I |
---|
Crossword |
Table 2
Column A | Column B | Column C | Column D | Column E | Column F | Column G |
---|---|---|---|---|---|---|
DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 | DAY 6 | |
WEEK 1 | Chess | |||||
WEEK 2 | Pilates | Elevate | ||||
WEEK 3 | Kundalini | Scrabble | Gardening | |||
WEEK 4 | Darning | Crossword | Chess | Pilates |
Created the custom function but the results are not what I was expecting from what I've been able to piece together.
答案1
得分: 2
The modulo operator % is used to wrap around to the beginning of alt_activities_range when the last activity is reached.
function calender_search(calendar_range, alt_activities_range) {
for (let week = calendar_range.length - 1; week >= 0; week--) {
for (let day = calendar_range[week].length - 1; day >= 0; day--) {
if (alt_activities_range.indexOf(calendar_range[week][day]) > -1) {
if (calendar_range[week][day] === alt_activities_range[alt_activities_range.length - 1]) {
return alt_activities_range[0];
}
// Go to index of match PLUS 1 for NEXT value to be returned
return alt_activities_range[(alt_activities_range.indexOf(calendar_range[week][day]) + 1) % alt_activities_range.length];
}
}
}
}
英文:
The modulo operator % is used to wrap around to the beginning of alt_activities_range when the last activity is reached.
function calender_search(calendar_range, alt_activities_range) {
for (let week = calendar_range.length - 1; week >= 0; week--) {
for (let day = calendar_range[week].length - 1; day >= 0; day--) {
if (alt_activities_range.indexOf(calendar_range[week][day]) > -1) {
if (calendar_range[week][day] === alt_activities_range[alt_activities_range.length - 1]) {
return alt_activities_range[0];
}
// Go to index of match PLUS 1 for NEXT value to be returned
return alt_activities_range[(alt_activities_range.indexOf(calendar_range[week][day]) + 1) % alt_activities_range.length];
}
}
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论