Gaining access to the spreadsheet is not difficult at all. You have to remember that while Google Apps Script runs on Google servers, the client-side code (e.g. HTML and JavaScript code you use in your UI templates) will be sent to your browser for rendering, so you can't really mix the two and write jQuery code in GAS(.gs) files or vice versa.
To clarify, commands like
var ss = SpreadsheetApp.openById("");
must be kept in .gs files. To use client-side HTML and JavaScript, you must create separate HTML files in your project (go to File - New - HTML file). Here's more information on serving HTML in GAS https://developers.google.com/apps-script/guides/html/
Luckily, Google provides the API that allows you to communicate between client and server sides by calling 'google.script.run.' followed by the name of the function in '.gs' file.
Example function in '.gs' file
function addRow() {
var sheet = SpreadsheetApp.getActive()
.getSheets()[0];
sheet.appendRow(['Calling', 'server', 'function']);
}
In your HTML template file, here's how you would call this function
<script>
google.script.run.addRow();
</script>
Consider the example that is more relevant to your situation. In my spreadsheet, the QUERY formula changes dynamically based on the value entered by the user. The form with input field is displayed in the sidebar.
Project structure
Code for 'sidebar.html' is below. Note that using the 'name' attribute of the <input> element is mandatory. On form submit, the value of the attribute ('filterBy') will be transformed into propetry of the form object that we can reference in our server function to get user input.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js">
</script>
</head>
<body>
<form id="myForm">
<input type="text" name="filterBy">
<input type="submit" value="submit">
</form>
<table id="myTable"></table>
<script>
$('document').ready(function(){
var form = $('#myForm');
var table = $('#myTable');
var runner = google.script.run;
form.on('submit', function(event){
event.preventDefault(); //prevents <form> redirecting to another page on submit
table.empty(); // clear the table
runner.withSuccessHandler(function(array){ //this callback function will be invoked after the 'retriveValues()' function below
for (var i = 0; i < array.length; i++) {
var item = '<tr><td>' + array[i] +'</td></tr>';
table.append(item);
}
})
.retrieveValues(this); //the function that will be called first. Here, 'this' refers to the form element
});
});
</script>
</body>
</html>
Code in '.gs' file:
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheets()[0];
function onOpen() {
var ui = SpreadsheetApp.getUi();
var htmlOutput = HtmlService.createTemplateFromFile('sidebar')
.evaluate();
ui.showSidebar(htmlOutput);
}
function retrieveValues(req) {
var res = [];
var filterBy = req.filterBy; //getting the value of the input field.
sheet.getRange(1, 2, 1, 1)
.setFormula("QUERY(A1:A, \"SELECT A WHERE A > " + filterBy + "\")");
sheet.getRange(1, 2, sheet.getLastRow(), 1)
.getValues()
.map(function(value){
if (value[0] != "") res = res.concat(value[0]); // get only the values that are not empty strings.
});
return res;
}
Here's the result of entering the value and submitting the form. The server-side function returns the array of values greater than 5. The callback function that we passed as parameter to 'withSuccessHandler' then receives this array and populates the table in the sidebar.
Finally, I'm not sure why you are using the QUERY formula. Instead of modifying 'SELECT' statement, you could simply take the values from the target range an filter them in GAS.
