CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheets
Nas
Nas

Reputation: 1

How to update HTML file when selecting a cell in Google Sheets using Google App Script

I tried to Google a solution for a while but couldn't find an answer.

I have a Google spreadsheet with an HTML file and a script, I want to be able to select any cell in the Google sheet and for its value to be populated directly in a textarea HTML element in my HTML file.

Once I am able to implement this functionality, I will be expanding on this be modifying the data before it's displayed in the HTML file, but this is outside the scope of this question.

The setup looks like this: enter image description here

I managed to get this done by adding the button on the top "Get Data" and using onclick with google.script.run.withSuccessHandler (please see the code below). However, I would like to be able to do the same without having to click the button every time.

<button id="Get Data" onclick="getData()"><b>Get Data</b></button>

<script>
      function getData() {
        google.script.run.withSuccessHandler(updateElement).activeCell();
      }

      function updateElement(values) {
        var column1Value = values[0];
        var column2Value = values[1];
        var column3Value = values[2];

        var column1 = document.getElementById('column1');
        column1.value = column1Value 

        var column2 = document.getElementById('column2');
        column2.value = column2Value 

        var column3 = document.getElementById('column3');
        column3.value = column3Value

        var column4 = document.getElementById('column4');
        column4.value = column3Value

        var doneMessage = document.getElementById('doneMessage');
        doneMessage.innerHTML = ""
      }
</script>

function activeCell(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowIndex = sheet.getActiveCell().getRow();
  var column1Value = sheet.getRange(rowIndex, 2).getValue();
  var column2Value = sheet.getRange(rowIndex, 3).getValue();
  var column3Value = sheet.getRange(rowIndex, 4).getValue();

  return [column1Value, column2Value, column3Value]
}

I tried using onmousedown, but it only works when having the mouse on the HTML element rather than the spreadsheet, I assume because the HTML element is an iframe.

I tried also onSelectionChange(e) which works really well until I couldn't figure out how to send the info of the selected cell to the HTML file.

Thanks for the help!

Upvotes: 0

Views: 278

Answers (1)

Waxim Corp
Waxim Corp

Reputation: 917

If I understand what you want to do correctly, I suggest a work-around to refresh the data with a timer instead of click

  • Wait for the page to load with addEventListener("DOMContentLoaded") in order to execute getData()
  • window.setTimeout() in the successHandler() function (you can modify duration)
  <script>
    window.addEventListener("DOMContentLoaded", (event) => {
      console.log("DOM loaded");
      getData();
    });

    function getData() {
      google.script.run.withSuccessHandler(updateElement).getData();
    }

    function updateElement(e) {
      //do stuff
      window.setTimeout(getData, 5*1000); //repeat getData() in 5sec
    }
  </script>

Upvotes: 1

Related Questions