How to use range variable from prompt response [answered]-CodePudding

I'm trying to make this simple function where I get the range & value I want to fill from the prompt response (so basically autofill given range with given value). But I get 'Exception: Range not found'. How can I use the response from prompt to work as range?

function myFunction(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi ();
  var response = ui.prompt("Enter Range:",ui.ButtonSet.OK_CANCEL);

  if(response.getSelectedButton() == ui.Button.OK){
    var inputvalue = ui.prompt("Set Value:",ui.ButtonSet.OK_CANCEL);
    var givenvalue = inputvalue.getResponseText();
    ss.getRange(response.getResponseText).setValue(givenvalue)
  }else if(response.getSelectedButton() == ui.Button.CANCEL){
  }
}

CodePudding user response：

Posting for documentation purposes.

As mentioned by Tanaike, since you missed the () in response.getResponseText(), you are passing the method getResponseText() instead of the string value returned by this method as a parameter for getRange(a1Notation). Since this is not a valid parameter for this method (it would require a string), you are getting this error.

Solution:

Replace this:

ss.getRange(response.getResponseText).setValue(givenvalue)

With this:

ss.getRange(response.getResponseText()).setValue(givenvalue);

CodePudding user response：

A Dialog to Enter Data into a Spreadsheet

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const r1 = ui.prompt("Enter Range in A1Notation(Remember to include Sheet Name):", ui.ButtonSet.OK_CANCEL);
  if (r1.getSelectedButton() == ui.Button.OK) {
    let r2 = ui.prompt("Enter value to place in range:", ui.ButtonSet.OK_CANCEL);
    if (r2.getSelectedButton() == ui.Button.OK) {
      let v = r2.getResponseText();
      ss.getRange(r1.getResponseText()).setValue(v)
    }
  }
}

ui promt