时间戳

首页 > 解决方案 > 谷歌脚本 - 函数不基于 if 语句循环

logging - 谷歌脚本 - 函数不基于 if 语句循环

问题描述

这是一个庞大的脚本（至少对我而言），它非常接近完成！

剩下的只是一些初期问题，所以我希望能得到一些帮助来完成它并工作，因为它已经超出了我的理解范围！

当前的问题是：

  1. 代码在第一次运行后不循环 - 不知道为什么？我不认为 i=1 是 < SessionNamesArraySize: 26 (minus 1, 25)
  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 脚本经验完成的，我已经学习和实施了大约一两个月。这是一切都是为了节省时间哈哈。

谢谢，米奇

标签： logginggoogle-sheets

解决方案

问题1，循环内的return结束循环：

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');

... ETC

函数可以简化为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()

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

如果您需要帮助，请 DM 我。祝一切顺利。

推荐阅读