CODEWITHSUNDEEP
phpgoogle-sheetsgoogle-apigoogle-drive-realtime-api
Abilash Erikson
Abilash Erikson

Reputation: 351

Embed interactive Google Sheet in website without letting users save changes [PHP ,HTML]

I want to embed a Google Sheets spreadsheet to my website. I would like it so that users can come to my website and they can see and interact with the spreadsheet. The spreadsheet also contains some formulae . For example there is a

width, height , fabric , price column

in this sheet . Fabric is a drop down and it contains some types of fabric with different prices. The formula for calculating the price is:

height*width*fabric value

So whenever user adds width, height, and selects the fabric then it outputs the corresponding value in the price field .

So I want to embed this google sheet in my websit, so that the user can use it to calculate prices without saving to original spreadsheet. I have a php website. If we can't do this using Google Sheets, then is there any way to upload this excel sheet to my website and do the same operation?

Please help.

Upvotes: 0

Views: 13879

Answers (4)

T2IV
T2IV

Reputation: 1

I've had the same issue with Google Sheets, but was able to solve the problem with Zoho Sheet. Recently Zoho made some changes to Zoho Sheet and their storage situation, so I'll have to confirm that it still work. Just an idea.

Upvotes: 0

user8395373
user8395373

Reputation:

I think I understand what you mean but I'm not entirely sure. From what you said in the question I am assuming that:

  • You have a working Google Sheets spreadsheet.
  • You have a website and you want to embed this spreadsheet into your website.
  • You want people to be able to interact with the spreadsheet, but without changing the formulae, etc...

Ok, so I think you have 2 main options here:

Option 1: embed spreadsheet with permissions

I realise that you have tried this option and that it is not working. Please see option 2. This first option involves taking the spreadsheet that you have already made and putting some editing permissions on it. I'm guessing that in your spreadsheet you have a cell with data validation on it (to create the dropdown menu. If not, can you please say how this is done) and that this is where the user selects an amount to input into your algorithm. If this is the case, then what you want to be doing is this:

  1. Open the spreadsheet in Google Sheets and go to the sheet which you want people to interact with.
  2. Along the bottom of your screen, locate the current sheet and click the little black arrow next to the name. Then click 'Protect Sheet'.
  3. A new window should open on the right. (Screenshot of the 'Protect Sheet' Window) In this new window, click 'Sheet' then in the dropdown below, select the sheet with which the user interacts.
  4. Next, tick the 'Except certain cells' box. In the new box, enter the reference to the user input cell. If you have more than one, click 'Add another range' and use this new box for another cell reference. Once your have completed this, click 'Set permissions'. When the new window appears, check that the 'Restrict who can edit this page' option is ticked and that 'Only you' is displayed underneath. Then click 'Done'.
  5. Now, to embed this file, what you need to do is go to the 'File' tab along the top left and click 'Publish to the web'. When the window appears, select 'Embed' then in the dropdown select the page that the user will interact with. Then click 'Publish' and hopefully you will be given an embed code that you can use on your website. Please try this out and get back to me with your results.

Option 2: Javascript form

I have found some HTML online and I have added some features to it to suit your needs. Please run the code below and see if it will work for you, and if it will, copy it and paste it into an HTML snippet section on your website builder. To change the fabrics and prices, just go into the code and edit them in the '' tags. If you need help with this please just ask. 

<!doctype HTML>
<html`enter code here` lang="en">
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible">
	<meta name="viewport" content="width=device-width, initial-scale=1">
<title>
Fabric price calculator
</title>
<head>
<style>
body {
	font-family: sans-serif;
	color: black;
}
input[type="text"] {
   color: black;
}

</style>
</head>
<body>
		<h2>Fabric price calculator</h2>	
		<form role="form" id="price" name="price">	
		<p>Select fabric type:</p>
		<select class="" id="itemselect" onchange="calculate()">
			<option id="itemprice" value="1.500">Cotton @ £1.50 / sq. m</option>
			<option id="itemprice" value="1.250">Denim @ £1.25 / sq. m</option>
			<option id="itemprice" value="3.000">Wool @ £3.00 / sq. m</option>
			<option id="itemprice" value="6.000">Silk @ £6.00 / sq. m</option>
			<option id="itemprice" value="4.000">Leather @ £4.00 / sq. m</option>
			<option id="itemprice" value="5.000">Nylon @ £5.00 / sq. m</option>
		</select>
		<p>Enter length and width of fabric:</p>
		<p><input type="number" id="qty" onchange="calculate()" value="" placeholder="Length (m)"></p>
		<p><input type="number" id="qtya" onchange="calculate()" value="" placeholder="Width (m)"></p>
		<p>Total price of fabric:</p>
		<p><input type="text" id="result" value="" readonly="readonly"></p>		
	</form>
	
	<script type="text/javascript">
		function calculate(price){
			var item = document.getElementById("itemselect").value || 0;
			var qty = document.getElementById("qty").value || 0;
			var qtya = document.getElementById("qtya").value || 0;
			
			item = parseFloat(item).toFixed(2);
			qty = parseFloat(qty).toFixed(2);
			qtya = parseFloat(qtya).toFixed(2);
			
			var result = parseFloat(item*qty*qtya).toFixed(2);
							
			document.getElementById("result").value="£"+result;
		}
	</script>
</body>
</html>

I hope this all works!

penguin_k

Upvotes: 3

Fabien
Fabien

Reputation: 4972

What I think you should do is simply make a COPY of your template or base spreadsheet for each user, so that it won't matter if they save it or not: each user would work on his own copy.

Eventually track down the copies so you can trash them on a regular basis (garbage collection).

Look at the COPY method of Google Drive's API:

https://developers.google.com/drive/v2/reference/files/copy

Example code in PHP:

/**
 * Copy an existing file.
 *
 * @param Google_Service_Drive $service Drive API service instance.
 * @param String $originFileId ID of the origin file to copy.
 * @param String $copyTitle Title of the copy.
 * @return DriveFile The copied file. NULL is returned if an API error occurred.
 */
function copyFile($service, $originFileId, $copyTitle) {
  $copiedFile = new Google_Service_Drive_DriveFile();
  $copiedFile->setTitle($copyTitle);
  try {
    return $service->files->copy($originFileId, $copiedFile);
  } catch (Exception $e) {
    print "An error occurred: " . $e->getMessage();
  }
  return NULL;
}

Note that you need permissions, but on the server-side. Just do not use a critical account, so you stay safe in case of server compromission.

Upvotes: 0

Ravinder Payal
Ravinder Payal

Reputation: 3031

This question is very much vague. Google Cheats is neither a programmable or API oriented service. Your need is a script which provides all the functionality of Excel but should not allow the CTRL+S.I mean saving the changes.

Here are libraries for making web based SpreadSheet Applications:- [Please see this link] https://jspreadsheets.com

Handsontable seems quite promising, but there are lots of libraries available you have to give a search. https://jspreadsheets.com/handsontable.html

Upvotes: 1

Related Questions