Providing basic functionality
This is a continuation, started here: Sheets Mirror App. Here’s how installing Sheets Mirror App at a basic level. You will learn to instantly publish price lists and schedules on the site. And for this you do not need to be a programmer or involve a specialist. All you need to do is copy the spreadsheet template and place the web page on your website (see below). After that, as soon as you copy the new sheet into the spreadsheet, the data from this sheet will immediately appear on the site. I am sure that under my leadership everything will work out easily, quickly and simply. If you have any difficulties, ask questions in the comments.
Installing Sheets Mirror App (backend)
Here is the installation of Sheets Mirror App in the part that is located in Google Sheets.
Spreadsheet template
The simplest thing you can do is make a copy of this spreadsheet. The template is designed for a situation where you need to regularly update the menu of your cafe on the website. Here the name of the sheet is the name of the food group.
A similar structure will be for a class schedule at a school or university (sheet name is the name of the class or study group), price list (sheet name is the name of the product group), etc.
Click on File/Make a copy.
The template has 4 visible sheets plus 1 hidden.
Setting up Sheets Mirror App
On the hidden Settings sheet, in cell B1, enter the address of the range of cells, the data from which should go to the site.
Note that this range address will be used to export data from all sheets. Therefore, make sure that the data to be exported starts in the same cell on each sheet.
Sheets Mirror App script
By copying the spreadsheet, you copied the script. You can view it and edit it by selecting Extensions/Apps Script from the menu.
Select Start Deployment/New Deployment.
Click on the gear and select the deployment type: Web Application.
Enter something meaningful in the Description field, and leave the Run as and Who has access fields filled in by default (On my behalf and Everyone).
Click on Start Deployment.
Next, 5 screenshots show how to grant access to the script.
Great. All that remains is to copy the URL (link) of the web application (click on OK) and click on Deploy.
Save the copied URL somewhere, for example, paste it into Notepad — it will come in handy in the frontend chapter.
Installing Sheets Mirror App (frontend)
Here we look at installing Sheets Mirror App in the part that will be located on your website.
Webpage
Copy this code into some text editor. This could be VS Code or even Notepad.
<!DOCTYPE html>
<!--
Free use of this code is permitted “AS IS” with reference to the author Baublys Boris
https://baublys.ru/sheets-mirror-app/
-->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Sheets Mirror App_menu</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style type="text/css">
TABLE {
width: 100%;
border-collapse: collapse;
}
TD,
TH {
text-align: center;
padding: 3px;
border: 1px solid black;
}
TH {
background: #b0e0e6;
}
.lc {
text-align: left;
}
.upd {
font-style: italic;
font-size: smaller;
text-align: center;
background: #b0e0e6;
}
</style>
</head>
<body>
<h1>Full Menu</h1>
<form id="uploadForm">
<p><select size="3" multiple name="group" id="myul">
<option disabled selected>Select group(s)</option>
</select>
</form>
<input value="Show" type="button" onclick="show();" />
<hr />
<div id="info"></div>
<dialog id="favDialog">
<form>
<p>
<label>
There is new data. Get?
</label>
</p>
<div>
<button value="cancel" formmethod="dialog">Cancel</button>
<button id="downloadBtn" value="Downloaded">Download</button>
</div>
</form>
</dialog>
<output></output>
<script>
const favDialog = document.getElementById("favDialog");
const outputBox = document.querySelector("output");
const downloadBtn = favDialog.querySelector("#downloadBtn");
favDialog.addEventListener("close", (e) => {
var val = '';
if (favDialog.returnValue === "default") {
val = "No return value.";
} else {
val = `ReturnValue: ${favDialog.returnValue}.`;
}
outputBox.value = val;
});
downloadBtn.addEventListener("click", (event) => {
event.preventDefault();
favDialog.close(downloadBtn.value);
localStorage.setItem('result', resweb);
makeSelect(resweb);
});
</script>
<script>
var resweb;
var resLS;
load();
resLS = getResultFromLS();
makeSelect(resLS);
function getResultFromLS() {
var resultLS = localStorage.getItem('result');
return resultLS;
}
function makeSelect(resultLS) {
var resultObj = JSON.parse(resultLS);
if (resultObj) {
var dropDown = document.getElementById('myul');
dropDown.options.length = 1;
for (i = 0; i < resultObj.length; i++) {
var listItem = document.createElement("option");
listItem.innerHTML = '<option>' + resultObj[i].group + '</option>';
dropDown.appendChild(listItem);
}
}
}
async function resWeb() {
try {
const url = 'WEB_PAGE_URL';
const response = await fetch(url);
if (!response.ok) {
throw new Error("HTTP error " + response.status);
}
const result = await response.json();
return result;
} catch (error) {
console.log(error);
}
}
async function load() {
try {
var data = await resWeb();
resweb = JSON.stringify(data.result);
if (resweb === resLS) {
console.log('No new data');
} else {
console.log('There is new data');
favDialog.show();
}
} catch (error) {
console.log(error);
}
}
</script>
<script>
function show() {
try {
var output = "<table>",
out;
var ls = localStorage.getItem('result');
var result = JSON.parse(ls);
for (var i = 0; i < result.length; i++) {
var obj = result[i];
let selectedOptions = Array.from(document.getElementById('myul').selectedOptions).map(({ value }) => value);
if (selectedOptions.includes(obj.group) === true) {
let now = obj.time;
output += "<tr><th>Group</th><th>" + obj.group + "</th><td class='upd' colspan='2'>Updated: " + now + "</td></tr>";
var o = obj.shed;
for (var j = 0; j < o.length; j++) {
var pair = o[j];
if (j == 0 && pair != null) {
output += "<tr><th>" + pair.join("</th><th>") + "</th></tr>";
} else {
if (pair != null) {
output += "<tr><td class='lc'>" + pair.join("</td><td>") + "</td></tr>";
}
}
}
output += "<tr><td></td></tr>";
}
}
} catch (e) {
console.log(e);
}
out = output + '</table>';
document.getElementById('info').innerHTML = out;
}
</script>
</body>
</html>Replace WEB_PAGE_URL in the code with the web application URL you saved earlier.
Save the code to index.html file.
Create a SheetsMirrorApp subdirectory on your site and upload the index.html file to it.
All! Basic functionality is provided, let’s check it out.
By going to your_website/SheetsMirrorApp/ in your browser, you will see the following:
The page displays the text Full Menu — we have it in the code above. If you have a price list or shedules, replace the text in the code of the index.html file.
The list of groups is empty because the data has not yet been loaded.
On the pop-up (pop-up window) it is written: There is new data. Get? Each time the data in the spreadsheet changes, such a pop-up will be shown to the user.
Of course, you can remove the part that displays this pop-up from the page code and update the data without further ado 😉
Now download the data — click on Download.
The list of groups will be populated and the text Downloaded will appear.
Now select one or more groups from the list and click on Show. The data will be displayed.
If you don’t need a choice, you can remove the list of groups and the Show button from the page code.
Now make changes to the spreadsheet. For example, change the text on some sheet. Please note that any time you edit a sheet, the date and time on that sheet will be updated.
After editing the data in the spreadsheet, refresh the web page. The content should also be updated.
What’s next?
Let me remind you that in the final article Sheets Mirror App: PWA I will talk about how to turn a web page into a mobile application and, in particular, teach it to display the contents of your site without the Internet.
Write your questions and wishes in the comments.
0 комментариев