如何在Google Apps脚本中的公式中使用变量列? [英] How to use variable columns inside my formula in Google Apps Script?

问题描述

我正在尝试在Google Apps脚本的公式中包含变量列.我只有这些列的编号作为信息-在另一张纸上定义为输入值.并且输入值可以更改.例如:我不想在公式中使用"G1"，而是通过其第7列显示.下面是我要实现的目标的简单表示.

I'm trying to include variable columns inside my formula in Google Apps Script. I only have the numbers of these columns as information - defined as input values on another sheet. And the input value can be changed. For example: I don't want to have "G1" in the formula, but present it through its column number 7. Below is a simple representation of what I'm trying to achieve.

function variableColumn() {
  var report = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = report.getSheetByName('Test');
  var inputSheet = report.getSheetByName('Input');

  // Current stored value in "A1" is 7 (corresponding to the "G" column)
  var firstColRange = inputSheet.getRange("A1");
  var firstColValue = firstColRange.getValues();

  // Current stored value in "A2" is 8 (corresponding to the "H" column) 
  var secondColRange = inputSheet.getRange("A2"); 
  var secondColValue = secondColRange.getValues();     

  var range = sheet.getRange("A1");
  range.setFormula('=G1 + H1');`

我期望最终结果是通过存储在输入"表中的列号(即通过变量firstColValue和secondColValue)显示"G1"和"H1".因为下次我可能想拥有('= P1 + T1').

What I'm expecting as a final result is to present "G1" and "H1" through their column numbers stored on the 'Input' sheet, i.e. through the variables: firstColValue AND secondColValue. Because next time I may want to have ('=P1 + T1').

非常感谢！

推荐答案

问题
OP尝试在构造要由setFormula分配的公式的过程中使用变量.

Problem
The OP is trying to use variables in the construction of a formula to be assigned by setFormula.

这不是一个新话题；例如请参阅 SpreadsheetApp如何在公式中使用变量或 Google App脚本:尝试为电子表格单元格设置公式.将OP的问题与众不同的是，OP不想这样使用变量名，而是要使用作为对列的引用的值.

This is not a new topic; e.g. refer SpreadsheetApp how to use variable inside formula or Google App Script: Trying to setFormula for Spreadsheet cell. What sets the OP's problem apart is that the OP doesn't want to use a variable name, as such, but rather a value that is a reference to a column.

解决方案

• 单元格A1和A2各自包含一个表示列号的数字.这些数字需要更改为字母才能包含在公式中.子例程columnToLetter(column)是执行此操作的有效方法.

• Cell A1 and A2 each contain a number that denotes a column number. Those numbers need to be changed to letters for inclusion in the formula. The subroutine columnToLetter(column) is an efficient way of doing this.

该公式将插入测试"表中.这要求公式中必须包含输入"工作表名称.对于如何获取工作表名称，我显示了两个选项:1)从变量值获取；和2)使用getSheetByName().getName().

The formula is inserted on the "Test" sheet. This requires that the "Input" sheet name must be included in the formula. I have shown two options for how the sheet name can be obtained: 1) from a variable value; and 2) using getSheetByName().getName().

可以将公式直接内置到setFormula中，也可以将其作为分配给setFormula的变量.后一种方法的好处是，在开发过程中，可以使用Logger语句显示公式的输出.这两个选项都包含在代码中.

The formula can be built directly into setFormula, or as a variable that is assigned to setFormula. The benefit of the latter approach is that, during development, a Logger statement can be used to display the output of the formula. Both options are included in the code.

关于转义字符" 在OP的情况下，工作表是单个单词，并且公式并不复杂到包含空格，逗号，冒号或正斜杠之类的字符.结果是不需要转义任何字符.如果不是这种情况，则有必要转义一些字符，在这种情况下，此关于转义字符的简要说明 ，以及有关该主题的代码示例，可能会有所帮助.

On "Escaping Characters" In the OP's scenario, the sheets are single words and the formula is no so complex as to include characters such as spaces, commas, colon, or forward slash. The effect is that no characters need be escaped. If this were not the case, then it would be necessary to escape some characters, in which case this brief note on Escaping Characters, and the code example on that topic, may be helpful.

function so5473414301() {

  // setup spreadsheet
  var report = SpreadsheetApp.getActiveSpreadsheet();

  // define sheets
  // Note: two ways to refer to a sheet name
  // 1 = put the name in a variable -> then getSheetByName(variable)
  // 2 = put the sheet name in method -> report.getSheetByName('Input'). Then get name using .getName()
  var mainSheetName = "Test";
  var mainSheet = report.getSheetByName(mainSheetName);
  //Logger.log("DEBUG: mainsheet name:"+mainSheet.getName()); //DEBUG

  var inputSheet = report.getSheetByName('Input');
  var inputSheetName = inputSheet.getName();
  //Logger.log("DEBUG: inputheet name: "+inputSheet.getName()); //DEBUG


  // Current stored value in "A1" is 7 (corresponding to the "G" column), and convert to the equivalent column letter
  var firstColRange = inputSheet.getRange("A1");
  var firstColValue = firstColRange.getValues();
  //Logger.log("DEBUG: fircolRange = "+firstColRange.getA1Notation()+", value = "+firstColValue); //DEBUG
  // get the columnletter for that column number
  var firstcolname = columnToLetter(firstColValue)
  //Logger.log("DEBUG: firstcolname = "+firstcolname); //DEBUG


  // Current stored value in "A2" is 8 (corresponding to the "H" column), and convert to the equivalent column letter
  var secondColRange = inputSheet.getRange("A2"); 
  var secondColValue = secondColRange.getValues();     
  //Logger.log("secondColRange = "+secondColRange.getA1Notation()+", value = "+secondColValue);
  // get the columnletter for that column number
  var secondcolname = columnToLetter(secondColValue);
  //Logger.log("DEBUG: secondcolname = "+secondcolname); //DEBUG


  // define the location for the formula
  var outputrange = mainSheet.getRange("A3");

  // construct the formula
  // The formula should output as '=Input!G1+Input!H1
  // Note 2 options here
  // 1 - Build the formula straight into the `setFormula`
  // 2 - Build the formula and assign it to a variable, then use the variable in the `setFormula`

  // Option#1
  //outputrange.setFormula("="+inputSheet.getName()+"!"+firstcolname+"1+"+inputSheet.getName()+"!"+secondcolname+"1");

  // Option#2
  // Build the formula - Looger will show the formula as converted.
  var formula = "="+inputSheet.getName()+"!"+firstcolname+"1+"+inputSheet.getName()+"!"+secondcolname+"1";
  Logger.log("DEBUG: The formula is: "+formula);//DEBUG

  // set the formula in the outputrange
  var output = outputrange.setFormula(formula);

}

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

---

单元格和公式值的汇总

信用 "columnToLetter"(这里不包括"letterToColumn") AdamL( https://stackoverflow.com/a/21231012/1330560 )

Credit "columnToLetter" (plus "letterToColumn" not included here) AdamL (https://stackoverflow.com/a/21231012/1330560)

这篇关于如何在Google Apps脚本中的公式中使用变量列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！