0

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.

enter image description here

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.

2
  • 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.
    – David Tan
    May 29 at 15:54
  • 1
    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.
    – Erik Tyler
    May 31 at 13:23

1 Answer 1

0

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

Sample

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.