Menu
I would like to publish a Google Spreadsheet on a webpage in playground mode which would enable the visitors to edit the content, change the formulas etc. but all those changes must remain local and not be saved in the original Google Spreadsheet.
Is it possible to publish and embed such a Google Spreadsheet like disabling the 'Save' functionality for end-users i.e. unable to auto-save on changes and can't save manually too?
Any solution will do that even if it involves Google Apps Script! Please advise. Thanks.
806
Please copy your recent edits then revert your changes.” If you encounter this message in Google Sheets, “Can't save your changes. Please copy your recent edits, then revert your changes,” it's because it took too long for the server to save your changes.
Click Set permissions or Change permissions. and choose an option: Only you: Only you (and the owner if you're not the owner) can edit the range or sheet. Only domain: If you use Google Sheets for work or school, only people in your domain can edit the range or sheet.
If you can't edit a file, a few things could be wrong: The file owner didn't give you permission to view the file. You're signed in to a different Google Account. Someone else with edit access removed your permission to edit.
Share using a link: Open the file and click Share. Under Get link, click Copy link. (Optional) To change permissions, click Change, then choose Viewer, Commenter, or Editor. Copy and paste the link in an email or any place you want to share it.
An easy method to maintain the integrity of a master spreadsheet while allowing people to edit is to just force a copy when they click the link.
Your spreadsheet needs, at minimum, view rights in the sharing settings. Then, grab the URL:
https://docs.google.com/spreadsheets/d/SOME_BIG_LONG_KEY/view
Change "view" at the end to "copy":
https://docs.google.com/spreadsheets/d/SOME_BIG_LONG_KEY/copy
The user will be asked to sign in and save a copy to their Drive. Your master remain untouched, everyone has a working copy of the sheet, and you don't need a big script to manage changes.
That being said, if you make changes to the master, they aren't reflected in the copy because it's a new document.
75
It is possible to put some of the functionality of a spreadsheet in a web app. This example is a trivial beginning to what you would want. As far as disabling saving that might be achieved by creating a copy of the spreadsheet you want and and the deleting it via DriveApp when you leave the web app (not sure how to do that). It might be possible but my guess is that it's a lot of work and I'm not sure it will perform as well as just using the standard spreadsheet. Brian Bennet's solution is probably a better idea but then that user keeps a copy of the spreadsheet that you have no control over. So that may not be what you want either.
Code.gs:
var SSID='SpreadsheetID';
var sheetName='Sheet Name';
function htmlSpreadsheet(mode)
{
var mode=(typeof(mode)!='undefined')?mode:'dialog';
var br='<br />';
var s='';
var hdrRows=1;
var ss=SpreadsheetApp.openById(SSID);
var sht=ss.getSheetByName(sheetName);
var rng=sht.getDataRange();
var rngA=rng.getValues();
s+='<table>';
for(var i=0;i<rngA.length;i++)
{
s+='<tr>';
for(var j=0;j<rngA[i].length;j++)
{
if(i<hdrRows)
{
s+='<th id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
}
else
{
s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
}
}
s+='</tr>';
}
s+='</table>';
//s+='<div id="success"></div>';
s+='</body></html>';
switch (mode)
{
case 'dialog':
var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
userInterface.append(s);
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Spreadsheet Data for ' + ss.getName() + ' Sheet: ' + sht.getName());
break;
case 'web':
var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
return userInterface.append(s).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
}
function updateSpreadsheet(i,j,value)
{
var ss=SpreadsheetApp.openById(SSID);
var sht=ss.getSheetByName(sheetName);
var rng=sht.getDataRange();
var rngA=rng.getValues();
rngA[i][j]=value;
rng.setValues(rngA);
var data = {'message':'Cell[' + Number(i + 1) + '][' + Number(j + 1) + '] Has been updated', 'ridx': i, 'cidx': j};
return data;
}
function doGet()
{
var output=htmlSpreadsheet('web');
return output;
}
htmlss.html:
<!DOCTYPE html>
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>
$(function() {
});
function updateSS(i,j)
{
var str='#txt' + String(i) + String(j);
var value=$(str).val();
$(str).css('background-color','#ffff00');
google.script.run
.withSuccessHandler(successHandler)
.updateSpreadsheet(i,j,value)
}
function successHandler(data)
{
$('#success').text(data.message);
$('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
}
console.log('My Code');
</script>
<style>
th{text-align:left}
</style>
</head>
<body>
<div id="success"></div>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
