How to pass Range Values into an HTML sidebar to be ticked or selected then save selections using GA-CodePudding

As the sidebar is open, it displays the colors (color name and its HEX #, which are sitting in a sheet) and displays also a checkbox next to each color.

The user ticks the checkbox or selects the colors how many he/she wants all the way down, clicks on save and the list gets saved into an array...maybe PropertiesService.getDocumentProperties()

Here's the code I got for the sidebar and getting the colors from the range:

var ss = SpreadsheetApp.getActiveSpreadsheet();

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

  var tmp = HtmlService.createTemplateFromFile('pickColor');
  var html = tmp.evaluate();
  html.setTitle('Escolha as cores');
  ui.showSidebar(html);
}

function getColors() {
  const colorListSheet = ss.getSheetByName('CadCores');
  const colorList = colorListSheet.getRange(2,1,colorListSheet.getLastRow(),2).getValues();
  return colorList;
}

Here's the html part, whose body I can't make it dynamic being a newbie. Listed Yellow as an example, but I suppose that having the checkbox, the color name and its hex would require it to be a table, correct?

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body>
  <input id="Yello" type="checkbox">
  <label for="yellow">Yellow</label>
  <br>
  <br>
  <button  id='salvar-cores'>Salvar Cores</button>
  <br>
  <br>
  <script>
    let colorArray = 10;//But this would be the getColors' length, right?
    function addColor(colors){
      for (let i = 0; i < colorArray; i  ){
        $(i   1).text(colors[i]);
      }
    };
    google.script.run.withSuccessHandler(addColor).getColors();
  </script>
</body>
</html>

Any direction is appreciated.

CodePudding user response：

I think this could work for you

1. I got all the colors in a spreadsheet

var ss = SpreadsheetApp.getActiveSpreadsheet();
  
function showSideBar() {
  var ui = SpreadsheetApp.getUi();

  var tmp = HtmlService.createTemplateFromFile('pickColor');
  var html = tmp.evaluate();
  html.setTitle('Escolha as cores');
  ui.showSidebar(html);
}

function getColors() {
  const colorListSheet = ss.getSheetByName('CadCores');
  const colorList = colorListSheet.getRange(2,1,colorListSheet.getLastRow() -1,2).getValues();
  return colorList;
}

function onOpen(){
  showSideBar()
}

2. Render the colors in the DOM

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div id='color-name'></div>
  <br>
  <button  id='salvar-cores'>Salvar Cores</button>

  <script>
    function afterLoad(){
      google.script.run.withSuccessHandler(afterDataReturned).getColors();
    }

    function afterDataReturned(arrayOfArrays){
      const item = document.getElementById("color-name")
      arrayOfArrays.forEach(function(row){

        let input = document.createElement("input")
        input.type = "checkbox"
        input.id = row[0]
        input.value = row[1]
        item.appendChild(input)

        let label = document.createElement("label")
        label.setAttribute('for',row[0])
        label.textContent = row[0]
        item.appendChild(label)

      })
    }

    document.addEventListener("DOMContentLoaded", afterLoad)
    
  </script>
</body>

</html>

1. I got all the colors in a spreadsheet

2. Render the colors in the DOM

3. Add any other color you want in the sheet and refresh the menu, so it reloads the new colors