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

ahmedmagdyahmedmagdy Posts: 4Questions: 0Answers: 0
edited April 26 in Free community support

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

Replies

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • ahmedmagdyahmedmagdy Posts: 4Questions: 0Answers: 0

    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 :)

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    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

  • ahmedmagdyahmedmagdy Posts: 4Questions: 0Answers: 0

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

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    edited 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

  • ahmedmagdyahmedmagdy Posts: 4Questions: 0Answers: 0

    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

Sign In or Register to comment.