How do I pass html data form a Google sidebar a variable in gs code

user_10284349 0
Viewed 812

I have a sheet which tracks pupil progress against a set of objectives - pupil name across the top, objectives listed down the left hand side. Each pupil is in a group - in the example below the groups are indicated by SK, IO and NU.

enter image description here

I have created a sidebar with various filters.

enter image description here

The issue I am having is with the filter where the user will enter a group name, when the filter button is clicked all of the columns will hide except for the columns which contain the entered group name. I have the following code:

from the .gs sheet:

function showGroup(group) {
  for(var i=2; i<lastCol-36; i++) {
    if(data.getCell(8, i).getValue() != group) { 
    sheet.hideColumns(i);
    }
  }
}

from the html sheet:

<form id="filterGroups" >
   <input type="text" value="" id="groupName">
   <input type="button" value="Filter"
     onclick="google.script.run.showGroup(filterGroups)" />

The closest I have got to success is to hide all columns providing the form id matches the parameter passed to google.script.run.

google-apps-script google-sheets html
1 Answers
  • You want to show only the columns with the values that user inputted. And you want to hide other columns except for them.
  • The values that user inputted are at row 8 of the sheet 'Science Year Group'.

If my understand of your question is correct, how about this modification?

Modification points:

  • If several strings (SK, NU) are put in the text box, if(data.getCell(8, i).getValue() != group){} is always false.
  • Give a name of name="groupName" to the text box. Using this, it retrieves the value using group.groupName at GAS side.
    • If the name is not given, you can retrieve the value using group[""].
  • I felt that when sheet.hideColumns(i) is used in the for loop, the process cost is high. So I used Sheets API for your situation.
    • By this, the hide and show of each column can be controlled by one API call.
    • At first, in order to use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Modified scripts:

GAS 
function showGroup(group) {
  var unhide = group.groupName.split(",").map(function(e){return e.trim().toUpperCase()});
  var values = sheet.getRange(8, 2, 1, (lastCol - 36) - 2).getValues()[0];
  var template = {updateDimensionProperties:{properties:{},range:{sheetId:sheet.getSheetId(),dimension:"COLUMNS"},fields:"hiddenByUser"}};
  var r = values.map(function(e, i) {
    var t = JSON.parse(JSON.stringify(template));
    if (~unhide.indexOf(e)) {
      t.updateDimensionProperties.properties.hiddenByUser = false;
    } else {
      t.updateDimensionProperties.properties.hiddenByUser = true;
    }
    t.updateDimensionProperties.range.startIndex = i + 1;
    t.updateDimensionProperties.range.endIndex = i + 2;
    return t;
  });
  Sheets.Spreadsheets.batchUpdate({"requests": r}, ss.getId());
}
HTML 
<form id="filterGroups" >
  <input type="text" value="" id="groupName" name="groupName">
  <input type="button" value="Filter" onclick="google.script.run.showGroup(filterGroups)">
</form>

Note:

  • In this modified script, when SK, NU is put in the text box, only columns with SK, NU are shown. Other columns are hidden.

Reference:

If I misunderstand your question, please tell me. I would like to modify it.

Edit :

About Can you show me how to pass html form data to GAS functions? of your comment, I show you a sample script using your HTML form. The HTML form is as follows.

HTML

In this sample, the value is sent to showGroup(filterGroups) using google.script.run().

<form id="filterGroups" >
  <input type="text" value="" id="groupName" name="groupName">
  <input type="button" value="Filter" onclick="google.script.run.showGroup(filterGroups)">
</form>
GAS

In this sample, the value from HTML form is retrieved by group.groupName.

function showGroup(group) {
  Logger.log(group.groupName)
}
user_71086530
Related