Embed just a range of editable google spreadsheet

Question

I have a google spreadsheet, and I give each of my users their own small range they can edit (just their own row, actually). Now I want to embed this sheet using iframes. How do I embed just a range of this editable spreadsheet? This line shows the desired range as I want it, but because of the "pubhtml?", it isn't editable:

src="https://docs.google.com/spreadsheets/d/1mjKXUsDs9EfqV9WztdfmNLm-sZwhphTieqEoBEHWce4/pubhtml?gid=0&single=true&widget=true&headers=false&range=a1%3Ah5&widget=false&chrome=false&rm=minimal"

When I change just the "pubhtml?" to "edit?", it becomes editable by those users as it should, but it shows me the entire sheet, including headers etc.:

src="https://docs.google.com/spreadsheets/d/1mjKXUsDs9EfqV9WztdfmNLm-sZwhphTieqEoBEHWce4/edit?gid=0&single=true&widget=true&headers=false&range=a1%3Ah5&widget=false&chrome=false&rm=minimal"

I can find a lot of (confusing) info on some of the parameters for embedding, but didn't find an answer to this problem. Or is it impossible? (Note: I did find two similar questions, but they didn't answer my problem - or maybe I didn't understand the answers? :-)

Thanks, Stef

Answer 1

Embedding Google Sheets does seem rather less intuitive than one might wish. But some research (remember, Google is your friend, unless perhaps if your name rhymes with Rump) and some trial and error have delivered a solution to your question.

The OP's plan is to provide each user with access to their own row for data entry. There are several ways that one might imagine that this could/would be done. The most obvious (to me) is simply to give the user access to "their row" on the master sheet - for instance, "user A" gets access to, say, row 53, "user B" gets access to row 17, and so on. This is quite easy to do (as we will see) BUT it is worthwhile/important that the user should also see the column headers. If, say, the column header is in row 1, then "user A" needs access to row 1 and row 53. Problem! We can give access to contiguous rows, but not to two discrete ranges. So this approach simply isn't possible - or at least I couldn't find a way to make it happen.

The approach that I took was to start with the master sheet. Then add one extra sheet for each user. For example, we add a sheet "User A", "User B" and so on. Each "User sheet" has only two rows of data. Row 1 contains column headers, Row 2 contains the user data - this gives us two contiguous rows that we can make accessible to the user. The cells in the master sheet change from containing hand-entered/hard-coded data to simple formulae that link to the appropriate column on the appropriate user sheet.

I don't believe that it is wise to give each user access to their own row (however it is that this might be done). In my opinion, the various security implications don't justify the risk. My strong recommendation would be that each user should have their own sheet (that is, a separate doc for each user). The user then gets access only to a limited number of rows in that sheet, and the master sheet (which is a separate file) contains formulae that pickup the data in the user sheet (also a separate file). With this approach, if a user manages to "screw up" (whatever/whenever/however - but you just know its gonna happen) then only their sheet (and the link to the master) is affected. This compares to an approach that would put the entire master spreadsheet at risk.

For the sake of completeness, I propose to address the various options - as eye-wateringly tedious as it may sound/be). For the sake of reference, I created a Google sheet (so_46059687) as a stand-in for the OP's master sheet.

Non-editable embed - (Version1)
In this example, one can see but not edit the master sheet. The document includes two "User" sheets but these are not visible (by choice).

I found two documents from the Google forums very helpful "Embed Sheet and remove Titles and Scrolling bars" and "How to I edit the height/width of a google sheets embed code when embedding it on my website?"

Codepen example

<iframe width="100%" height="250" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR-1keK8Wmyr4V6o6cjskLCetvsmbLeMsJuZViPpqkPck2-P2kCb4E4Ta_YMjbawz4lfgU_LVPFuqya/pubhtml?gid=0&amp;single=true&amp;widget=true&amp;headers=false"></iframe>

---

Non-editable embed - (Version2)
This is a variation on a theme of version 1 except that one of the user sheet has been made visible. The second user sheet is not visible (by choice).

Codepen example

<iframe width="100%" height="250" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR-1keK8Wmyr4V6o6cjskLCetvsmbLeMsJuZViPpqkPck2-P2kCb4E4Ta_YMjbawz4lfgU_LVPFuqya/pubhtml?widget=true&amp;headers=false"></iframe>

---

Editable embed - (Option#1)
This scenario shows a solution to the OP's question. The user can edit the data for their row. There is a downside - the user has complete access to their sheet. They can use rows and columns other than the ones linked to the master sheet, they can add a sheet but worse, they can delete their sheet.

These two invaluable articles explained how to create an editable embed: Google Docs: Embedding editable Google Docs and How to: Embed an editable Google Docs sheet.

Codepen example

<iframe width="100%" height="400" src="https://docs.google.com/spreadsheets/d/1XqT5umvq2vzK7CEivVJXTJdKlBW07bP9nnMMWs2px_Y/edit?usp=sharing"></iframe>

There are some things to note:

1. The user sheet called "Fred" has been "Share[d]". In this case, I choose "Anyone with the Link" so that it works in the Codepen, but in practise I would set the permissions so that only nominated people could edit the sheet.
2. The mastersheet is padlocked which means it is visible, but not editable. Another option would have been to hide the master sheet altogether but I chose otherwise in order to demonstrate the options open to the owner.
3. Note that the other user sheet (called "Joe") is not visible (because it hasn't been shared").
4. Note that whatever is edited on the user sheet (called "Fred") is immediately updated in master (called "Coy_Summary")
5. As noted, the user can delete their sheet or add an extra sheet. However this article (and code) "Google Spreadsheet Script To Insert And Delete Sheets with Protection" is apparently quite effective. I haven't tried it - I'll leave that to others.

Editable embed - (Option#2)
This is an example of how my recommendation might work. Each user (in this case, "Brian") has their own file (sheet), and the Master sheet picks up the user data with "IMPORTRANGE" function. Another aspect is that the user is limited to just two rows (though nothing limits access to extra columns).

Refer Google sheet

The document "How to embed specific cells range when embedding a Google spreadsheet" is essential reading. It explains in details how to limit the range that is embedded. Brilliant! "How to Share only Specific Sheet/Single Tab in Google Spreadsheet?" was also valuable.

Codepen example

<iframe width="250" height="75" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSzsA_yrb2uBCXywikOAbWrLnnEPYazevavza7PmtX9C6-xNw4p31gtCRBiCyxYkxVK7aMAWY1xZJ2o/pubhtml/sheet?headers=false&amp;gid=936292221&amp;range=A1:C2"></iframe>

BTW, if you look at the master sheet in Codepen Option#1, you can see the actual "importrange" formulae.

This is the formula for "Item #" in Column B of the master sheet:

=importrange("https://docs.google.com/spreadsheets/d/1-HtjEawH7p45qKY5c0syIOnTF15endnG4L8wIIPEaAs/edit?usp=sharing", "Brian!b2:b2")