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
ahmedmagdy
Posts: 4Questions: 0Answers: 0
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
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
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
Possibly you can update the
showData()
function to reload the table. Since you are passing in thecolumnsConfig
config I assume the columns might change when the Datatable is reloaded. If so you can usedestroy()
then reinitialize with the updateddataArray
. 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 useclear()
to clear the table rows followed byrows.add()
Kevin
I can add destroy() before showData() and call it reloadTables(), but i don't know how to trigger it properly
I would use
DataTable.isDataTable()
inshowData()
to see if its a Datatable then usedestroy()
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:
Kevin
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