Google脚本-函数不基于if语句循环

Question

这是一个庞大的脚本(至少对我来说)，它非常非常非常接近完成!

只是还有一些磨牙问题，所以我希望得到一些帮助，让它完成和工作，因为它接近超出我的理解!

当前的问题是:

1. 代码在第一次运行后不循环-不知道为什么?我不认为I =1是
2. "T"是不是数组中的第一项?不确定为什么它只拉一个单一字符-您可以在控制台日志
的末尾看到这一点

代码目的:为数组中的每个项目创建列-用G个表格公式填充它，这些公式从组合数据的2个选项卡中提取数据-一旦这部分完成，我将添加一些额外的最终函数。

电子表格:https://docs.google.com/spreadsheets/d/1aZBNp6b47-qp-1m-1dX0XtnGG2o1Nrs4AbckGInt0t8/edit gid = 878821914

控制台下面的代码…

代码如下:

//Build session reports
function allSessionNames2() {
//All Available variables
var TemplateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets();
Logger.log("TemplateSheet: " + TemplateSheet);
var TemplateTab = TemplateSheet[3].getName();
Logger.log("TemplateTab: " + TemplateTab);
var OverviewSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var overviewTab = OverviewSheet[0].getName();
var CurrentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[2];
var CurrentSpread = SpreadsheetApp.getActiveSpreadsheet();
var CurrentTab
var OnAIRSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var OnAIRTab = OnAIRSheet[2]
var CurrentSessionName = "";
var DetailsColumn;
FindDetailsColumn();
//Find the Details Column in OnAIR Tab
function FindDetailsColumn() {
for (var i = 1; i < OnAIRTab.getLastColumn() ; i++) {
if (OnAIRTab.getRange(1,i).getValue() == "Details") {
Logger.log("I: " + i);
DetailsColumn = i;
break;
}
}
}
var DetailsColumnLetter = ColumnToLetter(DetailsColumn);
//Convert DetailsColumn into letter
function ColumnToLetter(DetailsColumnLetter)
{
var Temp, Letter = '';
while (DetailsColumnLetter > 0)
{
Temp = (DetailsColumnLetter - 1) % 26;
Letter = String.fromCharCode(Temp + 65) + Letter;
DetailsColumnLetter = (DetailsColumnLetter - Temp - 1) / 26;
}
return Letter;
Logger.log("Letter: " + Letter);
}

Logger.log("DetailsColumn: " + DetailsColumn);
var DetailsColumnRange = DetailsColumnLetter + '2:' + DetailsColumnLetter;
Logger.log("DetailsColumnRange: " + DetailsColumnRange);
var DetailsColumnFilter = '=COUNTIF(' + DetailsColumnRange + ',' + DetailsColumnRange + ')=1';
DetailsColumnFilter.toString();
Logger.log("DetailsColumnFilter: " + DetailsColumnFilter);
//Filter Unique values in the details Column
function FilterUniqueDetailsColumn() {
var Spreadsheet = SpreadsheetApp.getActiveSheet();
Spreadsheet.getRange(DetailsColumnRange).activate();
var Criteria = SpreadsheetApp.newFilterCriteria()
.whenFormulaSatisfied(DetailsColumnFilter)
.build();
if (OnAIRTab.getFilter() != null) {
OnAIRTab.getFilter().remove();
}
//OnAIRTab.getFilter().getColumnFilterCriteria(9,Criteria);
OnAIRTab.getDataRange().createFilter().setColumnFilterCriteria(9,Criteria);
};
FilterUniqueDetailsColumn();
var LastRow = OnAIRTab.getLastRow();
var SessionNamesArray = OnAIRTab.getRange(2, DetailsColumn, LastRow, 1).getValues();
var Sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW OnAIR');
var Filter1 = Sheet1.getRange(DetailsColumnRange).getFilter();
var SessionNamesArrayUnique = [];
SessionNamesArray.forEach(function(x){
if(SessionNamesArrayUnique.indexOf(x[0]) === -1){
SessionNamesArrayUnique.push(x[0]);
}
});
//Filter1.getColumnFilterCriteria(DetailsColumn).getVisibleValues();
Logger.log("SessionNamesArrayUnique: " + SessionNamesArrayUnique);
var SessionNamesArraySize = SessionNamesArrayUnique.length;
var TemplateContentsA = "Session Name";
var TemplateContentsB = "Contact Identifier";
var Range1 = "B1";
var Range2 = "A1";
var Range3 = "A3";
var Range4 = "A4";
var Formula1 = "=iferror(FILTER('RAW OnAIR'!B:V,REGEXMATCH('RAW OnAIR'!O:O,B1)),"Session does not exist")";
var Formula2 = '=FILTER('RAW OnAIR'!1:1,'RAW OnAIR'!B1 = "Contact Identifier")';

//1. Fill Formula on Overview page
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A4").setFormula("=UNIQUE('RAW OnAIR'!P2:P)");
Logger.log("Fill Formula on Overview tab - cell A4")
//2. Retrieve all sessions into array
Logger.log("SessionNamesUniqueArray: " + SessionNamesArrayUnique);
Logger.log("SessionNamesArraySize: " + SessionNamesArraySize);
//3. Begin loop for creating a tab per session
for (var i = 1; i < SessionNamesArraySize - 1; i++) {
if (i == 1) {
Logger.log("Starting Tab creation loop");
Logger.log("Variable i: " + i);
} else {
Logger.log("restarting tab creation loop");
Logger.log("Variable i: " + i);
}
if (SessionNamesArrayUnique[i][0] === "") {
continue;
} else {
Logger.log("SessionNamesArrayUnique:" + SessionNamesArrayUnique[i][0]);
var ActiveSessionName = SessionNamesArrayUnique[i][0];
Logger.log("Current Session name: " + SessionNamesArrayUnique[i][0]);
Logger.log("Current Session name: " + ActiveSessionName);
//Create new sheet + paste code
CurrentSpread.insertSheet(ActiveSessionName);
//Paste code
Logger.log("Setting active range B1 - set value...");
CurrentSpread.setActiveSelection(Range1).setValue(ActiveSessionName);
CurrentSpread.setActiveSelection(Range2).setValue("Session Name:");
CurrentSpread.setActiveSelection(Range4).setFormula(Formula1);
CurrentSpread.setActiveSelection(Range3).setFormula(Formula2);
Logger.log("Setting values in cells.... Done.")

}
//4. Confirm tab count equals total session count minus the first 3 tabs
return Logger.log("Loop completed for sessions: " + ActiveSessionName);
}
}

控制台日志:

4:42:48 PM  Notice  Execution started
4:42:48 PM  Info    TemplateSheet: Sheet,Sheet,Sheet,Sheet
4:42:48 PM  Info    TemplateTab: Template
4:42:49 PM  Info    I: 9
4:42:49 PM  Info    DetailsColumn: 9
4:42:49 PM  Info    DetailsColumnRange: I2:I
4:42:49 PM  Info    DetailsColumnFilter: =COUNTIF(I2:I,I2:I)=1
4:42:53 PM  Info    SessionNamesArrayUnique: ,Tech Check Session for Moderators,AWM | Together to Win,Plenary,RECON | Real Intelligence,AWM | 2021 Channel Strategy,AWM | Hospital & Community Strategy  Execution,AWM | Pharmacy Strategy Execution,2. Global Supply Chain | Yasheen Nunkumar,1. Corporate Accounts & Government Affairs | Maroun El Khoury,3. Human Resources | Diana Da Silva,6. Regulatory & Quality | Minta Chen,Survey Test,4. Legal | Alan Boys,5. Medical Education | Mark Penno,6. Regulatory & Quality | Minta Chen v2,AWM | Pharmacy Team,AWM | Hospital Team,AWM | Community Team,SPM Day 2,RECON | Knees and Hips,AKOM 2021 Awards,AWM Day 3,SPM Day 3,RECON | Trauma,7. IT | Rohit Gupta
4:42:53 PM  Info    Fill Formula on Overview tab - cell A4
4:42:53 PM  Info    SessionNamesUniqueArray: ,Tech Check Session for Moderators,AWM | Together to Win,Plenary,RECON | Real Intelligence,AWM | 2021 Channel Strategy,AWM | Hospital & Community Strategy  Execution,AWM | Pharmacy Strategy Execution,2. Global Supply Chain | Yasheen Nunkumar,1. Corporate Accounts & Government Affairs | Maroun El Khoury,3. Human Resources | Diana Da Silva,6. Regulatory & Quality | Minta Chen,Survey Test,4. Legal | Alan Boys,5. Medical Education | Mark Penno,6. Regulatory & Quality | Minta Chen v2,AWM | Pharmacy Team,AWM | Hospital Team,AWM | Community Team,SPM Day 2,RECON | Knees and Hips,AKOM 2021 Awards,AWM Day 3,SPM Day 3,RECON | Trauma,7. IT | Rohit Gupta
4:42:53 PM  Info    SessionNamesArraySize: 26
4:42:53 PM  Info    Starting Tab creation loop
4:42:53 PM  Info    Variable i: 1
4:42:53 PM  Info    SessionNamesArrayUnique:T
4:42:53 PM  Info    Current Session name: T
4:42:53 PM  Info    Current Session name: T
4:42:53 PM  Info    Setting active range B1 - set value...
4:42:53 PM  Info    Setting values in cells.... Done.
4:42:53 PM  Info    Loop completed for sessions: T
4:42:55 PM  Notice  Execution completed

任何帮助将是伟大的-如果你看到的地方改进的代码，我会把它全部上船，这是完成与最低限度的G脚本的经验，我已经学习和实现了大约一个月或两个月。这都是为了节省时间，哈哈。

谢谢,米奇

Answer 1

第一次，循环内的返回结束了循环:

return Logger.log("Loop completed for sessions: " + ActiveSessionName);

---

问题2:

"T"是不是数组中的第一项?不知道为什么它只拉一个单一字符—您可以在控制台日志

的末尾看到它。

改变:

var ActiveSessionName = SessionNamesArrayUnique[i][0];

:

var ActiveSessionName = SessionNamesArrayUnique[i];

---

你可以在很多地方简化代码。

例如，调用一次工作表并获得当时的所有选项卡:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var TemplateTab = ss.getSheetByName('DATA OVERVIEW');
var OnAIRSheet = ss.getSheetByName('RAW OnAIR');

…等

FindDetailsColumn函数可以简化为一行:

var DetailsColumnLetter = String.fromCharCode(i + 64);

---

编辑:对于Z以外的列，您可以尝试:

var DetailsColumnRange = OnAIRTab.getRange(2, i).getA1Notation();
var colName = DetailsColumnRange.match(/D+/)[0];
DetailsColumnRange = DetailsColumnRange + ":" + colName;

---

最好不要从每个单元格调用数据表:

OnAIRTab.getRange(1, i).getValue()

最好获取整个数据范围(文档)并遍历它。

如果你需要帮助，发短信给我。祝你一切顺利。