How to embed non consecutive Google Sheets rows?

0
votes

I need to show only some rows of a Google Sheet, depending on a given data.

I'm embeding successfully the Google Sheet rows in my web site with the folowing iframe:

<iframe src="https://docs.google.com/spreadsheets/u/1/d/e/GOOGLE_SPREADSHEET_ID/pubhtml?gid=0&amp;single=true&amp;widget=false&amp;headers=false&amp;range=A10:C20" width="100%" height="500" style="border: none;"></iframe>

Now I want to include also the first row of my source Sheet having the columns names.

I tried sending the range data like range set range=A1:C1,A10:C10 but it is not allowed.

1
htmlgoogle-sheetsembed

1 Answers

2
votes

Answer:

You can not specify a discontinuous range in the URL when embedding a Spreadsheet.

Workaround:

What you can do instead is create a new sheet, in which the data from your sheet that you want to display is imported in a continuous way, and then select that sheet specifically when going to publish the sheet to the web.

You can import the data into a new sheet with the following formula in cell A1:

=TRANSPOSE(QUERY(TRANSPOSE(Sheet1!A1:C10), "select Col1, Col10"))

Making sure to change Sheet1 to be the name of the sheet with your data in.

Rundown of this formula:

  • Take the data in range Sheet1!A1:C10
  • TRANSPOSE it so the columns and rows are flipped (this is done so that you can select rows in the query)
  • Use QUERY to extract only the data from columns A and C
  • TRANSPOSE the data back so it looks as it does in the original sheet

You can then edit your embed URL to use this proxy sheet's Grid ID and specify the range:

.../pubhtml?gid=GRID_ID&amp;range=A1:C10&single=true&amp;...

The full URL (minus your Sheet and Grid ID):

<iframe src="https://docs.google.com/spreadsheets/u/1/d/e/GOOGLE_SPREADSHEET_ID/pubhtml?gid=GRID_ID&amp;range=A1:C10&single=true&amp;widget=false&amp;headers=false&amp;range=A10:C20" width="100%" height="500" style="border: none;"></iframe>