How to use range variable from prompt response

Question

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){
  }
}

I thought that the reason of your issue of Exception: Range not found, is due to getRange(response.getResponseText). In this case, the method of getResponseText is not run. Please add () like ss.getRange(response.getResponseText()).setValue(givenvalue), and test it again. I thought that there might be some duplicated questions in this case. But I couldn't find them. I apologize for this. — Tanaike
– Tanaike, Commented Nov 7, 2021 at 6:46
@Tanaike you were right, that was the problem apparently - I didn't even notice. Or to be honest I was't too sure on the rest of it so thought the problem might be elsewhere! thanks — Debs
– Debs, Commented Nov 7, 2021 at 6:54
Thank you for replying. I'm glad your issue was resolved. When your issue was resolved, can you post it as an answer? By this, it will be useful for other users who have the same issue. — Tanaike
– Tanaike, Commented Nov 7, 2021 at 6:55
Hi, for documentation purposes, please consider accepting the answer provided here stackoverflow.com/a/69880560. — Iamblichus
– Iamblichus, Commented Nov 11, 2021 at 9:54

Iamblichus · Accepted Answer · 2021-11-08 08:32:04Z

1

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

answered Nov 8, 2021 at 8:32

community wiki

Iamblichus

Sign up to request clarification or add additional context in comments.

Comments

Cooper · Accepted Answer · 2021-11-07 11:14:37Z

0

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

edited Nov 7, 2021 at 11:14

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

answered Nov 7, 2021 at 6:57

Debs

1339 bronze badges

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to use range variable from prompt response

2 Answers 2

Solution:

Comments

A Dialog to Enter Data into a Spreadsheet

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solution:

Comments

A Dialog to Enter Data into a Spreadsheet

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related