How to get user input in google app script?

Question

Here is what I want to achieve:

I want to delete n rows from my Google Sheet. This n can vary depending on the number of incorrect entries inserted in the document. (I know this number before running the function). And I want to give myself the flexibility to choose this number (just like console input in C, C++ or other languages).

Some researching shows a solution via SpreadsheetApp.getUi(). But it is giving me the error: Exception: Cannot call SpreadsheetApp.getUi() from this context.

I don't want to open my spreadsheet as it is huge in size & takes time to load. The purpose of deleting rows pragmatically is that I don't have to open it, else its all a moot point.

Another solution could be to just create an variable and change is manually before running script. But it could create bad data if I forget to change that variable someday. (I want to make it idiot-proof).

Is there any way to get user input for a standalone Google Apps Script without opening that particular Google Sheet?

Although I'm not sure whether I could correctly understand about your question, for example, to use Web Apps created by Google Apps Script includes your direction? Ref When Web Apps is used, for example, the function of Google Apps Script can be executed by the curl command. And also, you can access to the Web Apps using the browser. If this was not the direction you expect, I apologize. — Tanaike
– Tanaike, Commented Mar 2, 2021 at 12:08
Yes I thinks the same, @Tanaike webapp will do the work based on the input provided. — Gaurav
– Gaurav, Commented Mar 2, 2021 at 12:14
I think that to use Web Apps might be simpler. But, as another way, when you can use Sheets API, I think that the batchUpdate method of Sheets API might be able to be used. In this case, you can create the script by various languages and run the script on your terminal. And also, the process cost of Sheets API is lower than that of Spreadsheet service using SpreadsheetApp. Ref But I'm not sure whether this is the same direction you expect. I apologize for this again. — Tanaike
– Tanaike, Commented Mar 2, 2021 at 12:33
Thanks @Tanaike for the response. I will try to make web app work. — Paresh Nagore
– Paresh Nagore, Commented Mar 2, 2021 at 12:59

NightEye · Accepted Answer · 2021-03-02 14:41:47Z

2

You can always put the script into a blank sheet and treat it as a placeholder for your functions and have the ui prompt pop there. This way, you don't need to open your large sheet. You can always access other sheets when in another via Apps Script. This would be easier and you just need to transfer your script here.

Code:

function showPrompt() {
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.prompt(
      'Rows to delete?',
      'Input:',
      ui.ButtonSet.OK_CANCEL);

  var button = result.getSelectedButton();
  var numRows = result.getResponseText();
  if (button == ui.Button.OK) {
    // call function and pass the value
    deleteSheetRows(numRows);
  } 
}

function deleteSheetRows(numRows) {
  // url of the sheet with data
  var url = "https://docs.google.com/spreadsheets/d/***************/";
  var sheet = SpreadsheetApp.openByUrl(url);

  // do what you need to do here for that sheet using "numRows" value
  Logger.log("deleting "+numRows+" rows");
}

Output:

answered Mar 2, 2021 at 14:41

NightEye

11.3k2 gold badges8 silver badges25 bronze badges

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

1 Comment

Paresh Nagore Over a year ago

Great! This one does the job. Simple and easy

Gaurav · Accepted Answer · 2021-03-02 13:15:27Z

1

You can create a function in web app just write doGet() or doPost() function and call it with your input. refer https://developers.google.com/apps-script/guides/web

Take input number of rows which is n in your case, and add your code to delete rows from SpreadSheet.

you can pass input for get by using query parameter like:

?n=4

and you can use n in doGet() method.

edited Mar 2, 2021 at 13:15

answered Mar 2, 2021 at 12:11

Gaurav

3,7933 gold badges30 silver badges53 bronze badges

2 Comments

Paresh Nagore Over a year ago

Gaurav, do I need to write small html script to get input? When I deployed web app, by default I'm getting following output in the url: {"parameters":{},"contentLength":-1,"contextPath":"","queryString":"","parameter":{}}

Gaurav Over a year ago

You can call the web API (http) with your input as parameter directly no need of HTML, if you want you can also use html

Collectives™ on Stack Overflow

How to get user input in google app script?

2 Answers 2

Code:

Output:

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Code:

Output:

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related