Grouping the tables with dropdown box

Question

I am a beginner in google app script. So right now I am doing a project where users can sign in and can view their payment history. So for now it is just showing from 2020 until 2021. So I want your guys help on creating a dropdown box which states (eg : 2020 , 2021 ) so maybe if the user clicks 2020 then they can see the payment history of 2020 only. I really need your guys help in this thing. I have attached the link to my google app script and a image to explain myself better. Thank you guys.

https://script.google.com/d/1DdRKqUX__-ZITUgTZanQ_A7hUL1kcc0TZOeFmn58wYsX_o_7cqNExnYo/edit?usp=sharing - Link to my appscript

First image

Second Image

Kristkun · Accepted Answer

Here is a sample code you can refer with:

WebAppLogin.html (modifications)

Modifications done:

Include empty form-group class
Include hidden input to hold current logged-in user
Create a reusable function displayTable()
Create an html content for the drop-down filter. See variable filter.
Include another argument when calling GetRecords(username, filter)
Create a new function filter()
During initial log-in, filter will be set to "None". filter will be set depending on the option selected

Code.gs (modifications)

function GetRecords(username,filter) {
  var filteredDataRangeValues = GetUsernameAssociatedProperties(username);
  var resultArray = GetPaymentRecords(filteredDataRangeValues,filter);
  var resultFilter = getYears();

  result = {
    data: resultArray,
    filter: resultFilter
  };
  return result;
}

function getYears() { 
  var ss= SpreadsheetApp.openByUrl(url);
  var yearSheet = ss.getSheetByName("Configuration"); 
  var getLastRow = yearSheet.getLastRow();
  var return_array = [];
  for(var i = 2; i <= getLastRow; i++)
  {
      if(return_array.indexOf(yearSheet.getRange(i, 2).getDisplayValue()) === -1) {
        return_array.push(yearSheet.getRange(i, 2).getDisplayValue());
      }
  }
  return return_array;  
}

function GetPaymentRecords(userProperties,filter) {
  var transpose = m => m[0].map((_, i) => m.map(x => x[i]));
  var resultArray = [];
  var ss = SpreadsheetApp.openByUrl(url);
  var displaySheet = ss.getSheetByName(streetSheetName);
  var addressValues = displaySheet.getRange("B:C").getValues();
  var paidMonthValues = displaySheet.getRange("G:AD").getValues();
  //Logger.log(addressValues);
  //Logger.log(transpose(paidMonthValues));
  userProperties.forEach((v, i) => {
    var userHouseNumber = v[1];
    var userStreet = v[2];
    var column = addressValues.reduce(function callbackFn(accumulator, currentValue, index, array) {
      if (currentValue[0] == userHouseNumber && currentValue[1] == userStreet) {
        return index
      } else {
        return accumulator
      }
    }, '');
    //Logger.log(column);
    Logger.log(filter)
    Logger.log(paidMonthValues);
    
    if(filter=="None"){
      var result = transpose(paidMonthValues).map(function callbackFn(element, index, array) {
        return [element[0], userHouseNumber, userStreet, element[column] || '']
      });
    }else{
      var result = transpose(paidMonthValues).map(function callbackFn(element, index, array) {
        if(element[0].includes(filter))return [element[0], userHouseNumber, userStreet, element[column] || '']
      });
    }
    
    resultArray = resultArray.concat(result);
    //Logger.log(resultArray);  
  })

  //Remove null elements
  resultArray = resultArray.filter(element=>{
    Logger.log(element!=null)
    return element != null;
  });
  return resultArray;
}

Modifications done:

Modified GetRecords() and GetPaymentRecords() to include filter option
Add removal of null elements in the resultArray. (Null elements may exist when filter option was used due to the map() used)

Output:

(After user logged-in)

(After user selects a filter)

(UPDATE):

The following modifications where done to create a drop-box based on the list of years available in the configuration sheet.

WebAppLogin.html

displayTable() was modified that will accept an object as its parameter which contains an array data and an array of filter strings.
displayTable() was modified to update the drop-down options based on the filter strings available

Code.gs

getYears() was added that will read the sheet "Configuration" to get the filter string values
GetRecords() was modified to return an object which contains an array of record data and an array of filter strings.

Grouping the tables with dropdown box

Answers (1)

Here is a sample code you can refer with:

Output:

(UPDATE):

Related Questions