Automatically update tables' data when google sheets is modified on Google Apps Script

ahmedmagdy · April 26

I have 2 tables, one called pending and another called approved.
When a user posts something, it goes to pending awaiting approval.
After approving the post, the sheet gets edited but i need to refresh the page to get the right info in the tables
Can i do a trigger that knows when the google sheets file changes ( either manually or by codes ), and get the tables to update?

Here's my index.html

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
  <script>

function approveRow(rowIdx, tableNumber) {
  // Get the data from the pending table
  var rowData = $('#pending-posts-table-' + tableNumber).DataTable().row(rowIdx).data();

  // Move the row data to the approved sheet
  google.script.run.withSuccessHandler(function() {
    // Refresh tables after the data has been successfully moved
    refreshTables();
  }).approvePendingPost(rowData, tableNumber);
}

    function showData(dataArray, tableId, tableNumber, columnsConfig) {
      $(document).ready(function() {
        $(tableId).DataTable({
          data: dataArray,
          columns: columnsConfig.map(function(column) {
            if (column === 'Approval') {
              return {
                "title": column,
                "render": function(data, type, row, meta) {
                  return '<button onclick="approveRow(' + meta.row + ',' + tableNumber + ')">Approve</button>';
                }
              };
            } else {
              return {"title": column};
            }
          })
        });
      });
    }

    function ShowTables() {
var tableNumbers = [];
for (var i = 1; i <= 1; i++) {tableNumbers.push(i);}

      // Define column configurations for each table
      var columnConfigs = {
        1: {
          pending: ["Publisher", "Subject", "Body", "Data Created", "Approval"],
          approved: ["Publisher", "Subject", "Body", "Data Created", "Status"]
        },
        // Add more configurations for other tables as needed
      };

      // Loop through each table number and fetch data
      tableNumbers.forEach(function(tableNumber) {
        var pendingColumns = columnConfigs[tableNumber].pending;
        var approvedColumns = columnConfigs[tableNumber].approved;

        google.script.run.withSuccessHandler(function(dataArray) {
          showData(dataArray, '#pending-posts-table-' + tableNumber, tableNumber, pendingColumns);
        }).getPendingData(tableNumber);

        google.script.run.withSuccessHandler(function(dataArray) {
          showData(dataArray, '#approved-posts-table-' + tableNumber, tableNumber, approvedColumns);
        }).getApprovedData(tableNumber);
      });
    }

    $(document).ready(function() {
      // Define the range of table numbers you have
ShowTables();
    });
  </script>
</head>
<body>
  <!-- For each table, replace the table ID and the div ID -->
  <!-- Example for table number 1 -->
  <div>
    Pending Posts
    <table id="pending-posts-table-1">
      <!-- showData() function defined above would inject table data here -->
    </table>
  </div>
  <div>
    Approved Posts
    <table id="approved-posts-table-1">
      <!-- showData() function defined above would inject table data here -->
    </table>
  </div>
</body>
</html>

And my code.gs

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index').evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

var spreadSheetId = "123";    // REPLACE WITH YOUR ID

function getRangeString(tableNumber) {
  if (tableNumber === 1) {
    return "A2:E";
  } else {
    // Handle other table numbers as needed
    return ""; // Default case, you may want to handle this differently
  }
}


function getPendingData(tableNumber) {
  var sheetName = "PostsSheetPending" + tableNumber;
  var range = getRangeString(tableNumber);
  return Sheets.Spreadsheets.Values.get(spreadSheetId, sheetName + "!" + range).values;
}

function getApprovedData(tableNumber) {
  var sheetName = "PostsSheetApproved" + tableNumber;
  var range = getRangeString(tableNumber);
  return Sheets.Spreadsheets.Values.get(spreadSheetId, sheetName + "!" + range).values;
}

function approvePendingPost(rowData, tableNumber) {
  var pendingSheetName = "PostsSheetPending" + tableNumber;
  var approvedSheetName = "PostsSheetApproved" + tableNumber;
  var range = getRangeString(tableNumber);

  // Check if the row has already been moved to the approved sheet
  var isAlreadyApproved = SpreadsheetApp.openById(spreadSheetId).getSheetByName(approvedSheetName).getDataRange().getValues().some(function(row) {
    return row[0] == rowData[0] && row[1] == rowData[1] && row[2] == rowData[2] && row[3] == rowData[3];
  });

  if (!isAlreadyApproved) {
    // Move the row to the approved sheet with the updated status
    var targetSheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName(approvedSheetName);
    var valuesToMove = [rowData.slice(0, 4).concat(["Approved by x"])]; // Concatenate the approved status
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, valuesToMove.length, valuesToMove[0].length).setValues(valuesToMove);

    // Remove the row from the pending sheet
    var sheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName(pendingSheetName);
    var data = sheet.getDataRange().getValues();
    var rowIndex = data.findIndex(function(row) {
      return row[0] == rowData[0] && row[1] == rowData[1] && row[2] == rowData[2] && row[3] == rowData[3];
    });
    if (rowIndex != -1) {
      sheet.deleteRow(rowIndex + 1);
    }
  }
}

function include(filename) { 
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

Edited by Allan - syntax highlighting

allan · April 26

Can i do a trigger that knows when the google sheets file changes

You'd need to refer to the Google documentation to see if they fire an event when the sheet data changes I'm afraid. I've no idea about that.

Allan

ahmedmagdy · April 26

There's an onEdit() and onChange() triggers, but they may be working on user changes only

You can find them here:
https://developers.google.com/apps-script/guides/triggers

And I also don't have an idea on how to reload the tables

kthorngren · April 26

how to reload the tables

Possibly you can update the showData() function to reload the table. Since you are passing in the columnsConfig config I assume the columns might change when the Datatable is reloaded. If so you can use destroy() then reinitialize with the updated dataArray. See the second example in the docs.

Or if the columns don't change you can use DataTable.isDataTable() to see if the Datatable exists. If not initialize the Datatable as normal. Otherwise use clear() to clear the table rows followed by rows.add()

Kevin

ahmedmagdy · April 26

I can add destroy() before showData() and call it reloadTables(), but i don't know how to trigger it properly

kthorngren · April 26

I would use DataTable.isDataTable() in showData() to see if its a Datatable then use destroy() if it is.

Looking at your function you probably will want to remove the $(document).ready) event so the function runs when you call it. See commented lines below.

Something like this:

    function showData(dataArray, tableId, tableNumber, columnsConfig) {
      // $(document).ready(function() {  remove this line

        // Destroy Datatable if it exists
        if (!DataTable.isDataTable( tableId )) {
            $(tableId).DataTable().destroy();
            $(tableId).empty();
        }

        // Initialize Datatable
        $(tableId).DataTable({
          data: dataArray,
          columns: columnsConfig.map(function(column) {
            if (column === 'Approval') {
              return {
                "title": column,
                "render": function(data, type, row, meta) {
                  return '<button onclick="approveRow(' + meta.row + ',' + tableNumber + ')">Approve</button>';
                }
              };
            } else {
              return {"title": column};
            }
          })
        });
      // }); remove this line corresponding to $(document).ready
    }

Kevin

ahmedmagdy · April 26

That worked perfectly

I might repeat the code every 10 seconds until i find a way to make it detect changes that are with "approval" buttons, because it now detects manual changes only

Automatically update tables' data when google sheets is modified on Google Apps Script

Automatically update tables' data when google sheets is modified on Google Apps Script

Replies

Howdy, Stranger!

Categories

DataTables

Automatically update tables' data when google sheets is modified on Google Apps Script

Automatically update tables' data when google sheets is modified on Google Apps Script

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables