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

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

• Welcome to Web apps Stack Exchange! Take the tour here. Where are they inputting the number? Couldn't you just place a reference a cell where the user will place the number? Or if you want a prompt to show up, look into this. May 29 at 15:54
• It's hard to know, because you haven't shared a link to your spreadsheet or even an image of it. But it seems to me that the entire formula could be a lot more efficient. Consider sharing a link to the spreadsheet (or to a copy of it), including realistic data, indicating where the user will make the entry, and showing your hand-entered expected result for that entry. May 31 at 13:23

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 sheet = ss.getSheetByName("Sheet2")

// value is from user input
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.');
}

}
``````

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. 