CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-query-language
Sebastian Murillo
Sebastian Murillo

Reputation: 43

Is it possible to do Input-based queries in Apps Script?

I want to develop a function that:

  1. Asks for text input.

  2. Makes a query formula on a determined cell (AND includes the input text).

  3. Flattens the query (or copypastes the data as values).

I am having trouble with the second step because I can't use "" quotes two times and "text" is not being detected as a variable. Is this even possible? If so, do you have any ideas how to make it work?

function queryProviderData() {

  //Ask for text input.
  var ui = SpreadsheetApp.getUi(); 
  var result = ui.prompt(
      '¡Carguemos la información!',
      'Ingresa el nombre del proveedor:',
      ui.ButtonSet.OK_CANCEL);

  //Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    ui.alert('El nombre del proveedor es ' + text + '.');
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    ui.alert('No entendí la información.');
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.
    ui.alert('Cerraste el diálogo.');
  }  

  //Make query including text input.
  var sheet = SpreadsheetApp.getActive().getSheetByName('SheetB');
  var cell = sheet.getRange("A1");
  cell.setFormula("=QUERY('Sheet1'!A1:H67;\"SELECT * where H= "text"; -1)");
}

Running this function I am getting this error message:

"Missing ) after argument list. (line 73, file "Buttons")Dismiss."

Upvotes: 1

Views: 179

Answers (1)

Mahmoud Maarouf
Mahmoud Maarouf

Reputation: 185

It's possible!

Javascript allows " " and ' ' (double and single quotes) for a string, so you could use either and incorporate one of them into the string.

Furthermore, you could use the backslash character to treat the quote as a character instead of the string termination.

So you could do:

H= ' "+text+" ';

or

H = "\"+text+\"";

Let me know if you need anything else!

Upvotes: 1

Related Questions