How to use variable columns inside my formula in Google Apps Script?

Question

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

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

Many thanks in advance!

Answer 1

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

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.

Solution

• 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.

• 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().

• 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.

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;
}

---

Summary of cell and formula values

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