Google sheets -How to modify a number in a formula based on user input

Question

I was trying to automate a project where I would need to modify a number in one of the formula every time as per the user input.

For example: in the above formula, the highlighted number (9) should be changed as per user input (Maybe using a prompt or the data from another cell).

Could anyone please help me to achieve this? I will really appreciate your suggestions on this.

Answer 1

A value in a formula can be created by hand (hard-coded), a cell reference, a named range, and by user input. Which of these is best/most appropriate in a given situation depends on the user's needs.

This answer doesn't use the formula in the question because of unknown values that relate to that formula, as well as what appear to be syntax problems with that formula. However, I used a formula that included the arrayformula feature to ensure that the formula values would apply in that case.

In the following answer, the formula includes sequence and the variable is the fourth parameter "step"

Hard-coded

• =arrayformula(vlookup(sequence(3,1,2,4),$A$2:$B$20,2,0))

Cell reference

• =arrayformula(vlookup(sequence(3,1,2,D7),$A$2:$B$20,2,0))

Named range

• =arrayformula(vlookup(sequence(3,1,2,Sheet2!myVariable),$A$2:$B$20,2,0))

User input

function formulaWithInput() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Sheet2")
   
  // value is from user input
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Formula variable?', ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  if (response.getSelectedButton() == ui.Button.OK) {

    var cell = sheet.getRange("D18")
    var userInput = sheet.getRange("D17")
    //Logger.log('DEBUG: The user variable is %s.', response.getResponseText())
    userInput.setValue(response.getResponseText())
    cell.setFormula("=arrayformula(vlookup(sequence(3,1,2,"+response.getResponseText()+"),$A$2:$B$20,2,0))")
  } else if (response.getSelectedButton() == ui.Button.CANCEL) {
    Logger.log('The user didn\'t want to provide a variable.')
  } else {
    Logger.log('The user clicked the close button in the dialog\'s title bar.');
  }

}

What about a Custom Function?

You can't use a Custom Function to use setFormula with Google appScript. @Rubén describes the options in Do not have permission to use setFormula Google appScript on StackOverflow.

Of course, you could create a Custom Function that recreates your formula using appScript. But that would be a task for you to pursue.

---

Sample