Sending filtered HTML web app table data back to Google Sheets using Apps Scripts

Question

Currently, I'm trying to pull the filtered district data from my table and submit it to a column in my google sheets. I'm able to manually input data directly into my google sheets, but I can't figure out how to pull the data I've filtered in my data and input it into the sheets.

function onSuccess() {
  alert('Your request has ben submitted');
}

function buttonFunction() {
  var table = document.getElementById("myTable");
  var rows = table.getElementsByTagName("tr");
  var cells = rows.getElementsByTagName("td");
  var cellvalue = (cells[0].innerText);

  google.script.run.withSuccessHandler(onSuccess).setValue(cellvalue);
}

function generateTable(dataArray) {
      var tbody = document.getElementById("table-body");
      var counter = 0;
      for (var row in dataArray)  {
        var row = document.createElement("tr");
        var col1 = document.createElement("td");
        var col2 = document.createElement("td");
        var col3 = document.createElement("td");
        var col4 = document.createElement("td");
        var col5 = document.createElement("td");
        var col6 = document.createElement("td");

        col1.textContent = dataArray[counter][0];
        col2.textContent = dataArray[counter][1];
        col3.textContent = dataArray[counter][2];
        col4.textContent = dataArray[counter][3];
        col5.textContent = dataArray[counter][4];
        col6.textContent = dataArray[counter][5];

        row.appendChild(col1);
        row.appendChild(col2);
        row.appendChild(col3);
        row.appendChild(col4);
        row.appendChild(col5);
        row.appendChild(col6);

        tbody.appendChild(row);
        counter +=1; 
      }
}

function filterFunction() {
  // Declare variables
  var input, filter, table, tr, td, i, txtValue;
  input = document.getElementById("myInput");
  filter = input.value.toUpperCase();
  table = document.getElementById("myTable");
  tr = table.getElementsByTagName("tr");

  // Loop through all table rows, and hide those who don't match the search query
  for (i = 0; i < tr.length; i++) {
    td = tr[i].getElementsByTagName("td")[3]
    
    if (td ) {
      txtValue = td.textContent || td.innerText;
      if (txtValue.toUpperCase().indexOf(filter) > -1) {
        tr[i].style.display = "";
      } else {
        tr[i].style.display = "none";
      }
    }
  }
}

setValue function

function setValue(value) {
  var ss = SpreadsheetApp.openByUrl(url);
  var targetSheet = ss.getSheetByName('Target Lists');
  targetSheet.getRange(1,ws.getLastColumn()+1,1,2).setValue(value);
}

table html

<table class="table table-hover" id="myTable" 
     data-custom-sort="customSort"
  data-pagination="true"
  data-ajax-options="ajaxOptions"
  data-url="json/data1.json">
                <div class="dropdown">
                    <button class="btn btn-secondary dropdown-toggle" type="button" id="dropdownMenuButton" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                        District
                    </button>
                    <input type="text" placeholder="Search.." id="myInput" onkeyup="filterFunction()">
                    <div class="dropdown-menu" aria-labelledby="dropdownMenuButton">
                        <a class="dropdown-item" href="#">Emery Unified</a>
                        <a class="dropdown-item" href="#">Mountain House Elementary</a>
                    </div>
                </div>
                <thead>
                    <tr>
                        <th scope="col">County</th>
                        <th scope="col">District</th>
                        <th scope="col">Position</th>
                        <th scope="col">Full Name</th>
                        <th scope="col">Date</th>
                        <th scope="col">Note</th>
                    </tr>
                </thead>
                <tbody id="table-body">

                </tbody>
            </table>
            <input type="text" class="form-control" id="listName" placeholder="Type Name Here" aria-label="Username" aria-describedby="basic-addon1">
            <button class="btn btn-dark" type="save" id="saveButton">Save</button>
        </div> <!-- CLOSE AUTOFLOW -->
        <button onclick='buttonFunction()'>Submit</button>

You can ignore my extra submit functions-I was testing out some other options.

I have to apologize for my poor English skill. I cannot understand about pull the data I've filtered in my data and input it into the sheets.. Can I ask you about the detail of it? — Tanaike
– Tanaike, Commented Jul 27, 2020 at 23:24
Sure no problem! For my table, I have a function that filters my table of school districts by searching up the name of the superintendent/teacher. I want to be able to filter the school district names in my my table (which is in one column) and put all the filtered list of districts into a column in a google sheets that I have using the google sheets api. For example, if I filter 3 school districts in my table based on an input in my search bar, I want to be able to press a button that makes a call and populates a column into a google sheet that I choose with those 3 school districts. — shotaro808
– shotaro808, Commented Jul 28, 2020 at 4:35
I can already make changes to my google sheet using a javascript method that I created, but I don't know how to add the information from my filtered table into the google sheets. Thanks! — shotaro808
– shotaro808, Commented Jul 28, 2020 at 4:36
Thank you for replying. I could understand about my filtered table. But I cannot understand about what you want to do from populates a column into a google sheet that I choose with those 3 school districts. So, I proposed the modified script by my guess for your goal. Could you please confirm it? If my guess was not correct, can I ask you about the detail of your goal? — Tanaike
– Tanaike, Commented Jul 28, 2020 at 5:56

Tanaike · Accepted Answer · 2020-07-28 05:56:25Z

I believe your goal as follows.

You want to retrieve the showing values from the filtered table of HTML and put them to the Google Spreadsheet.

At first, please confirm the following modification points.

Modification points:

At HTML&Javascript
- In this case, it is required to retrieve the value from td in tr that style.display is not none.
At Google Apps Script,
- It seems that url and ws are not declared.
- I cannot understand what you expect from targetSheet.getRange(1,ws.getLastColumn()+1,1,2).setValue(value).
  - So from your replying, I supposes that you want to append value to targetSheet in the Spreadsheet of url.

When above points are reflected to your script, it becomes as follows.

Modified script:

HTML&Javascript:

Please modify buttonFunction() as follows.

function buttonFunction() {
  var table = document.getElementById("myTable");
  var values = [...table.querySelectorAll("tr")].reduce((ar, tr) => {
    if (tr.style.display != "none") {
      var temp = [...tr.querySelectorAll("td")].map((td) => td.innerHTML);
      if (temp.some((e) => e.toString() != "")) ar.push(temp);
    }
    return ar;
  }, []);

  console.log(values); // Here, you can see the retrieved values at the console.

  google.script.run.withSuccessHandler(onSuccess).setValue(values);
}

Google Apps Script:

Please modify setValue(value) as follows.

function setValue(value) {
  var url = "###"; // Please set this.
  var ss = SpreadsheetApp.openByUrl(url);
  var targetSheet = ss.getSheetByName('Target Lists');
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, value.length, value[0].length).setValues(value);
}

Note:

From your replying, I supposes that you want to append value to targetSheet in the Spreadsheet of url. If my guess was not correct, can I ask you about the detail of your goal?
If you are using Web Apps, after you modified above script, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

Collectives™ on Stack Overflow

Sending filtered HTML web app table data back to Google Sheets using Apps Scripts

1 Answer 1

Modification points:

Modified script:

HTML&Javascript:

Google Apps Script:

Note:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

HTML&Javascript:

Google Apps Script:

Note:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related