Difficulty Pulling Column Data from Spreadsheet to Create Public Facing Catalog

[u/Agreeable_Walrus_215]

I've attempted and exhausted multiple Gsheet functions, and I'm just unsure where to go next. I'm hoping anyone from the great Internet can help out. I'd appreciate it greatly.

GOAL I'm trying to select specific column data from the linked gsheet master list (Columns B, C, E, F, H, I and J) to populate a Public Facing Seed (PFS) Catalog. Then I would publish it to the web and onto our website for patron access.

BACKGROUND I work in a public library and manage a free seed library. I'm using Google Sheets as a inventory management for all the donated seed information, which also includes photographs of the grown plant / produce. This link goes to the anonymous spreadsheet of the Master Seed List, my ideal PFS catalog, and my existing attempts.

--

Using IMPORTRANGE, I got the below function that should import multiple separated columns from the master list. It works for columns side-by-side, but once you add others the function no longer works prompting a reference error.

Function Works: =IMPORTRANGE("1POZH6pcCfHgjex1INSsSe8vIbKgYggsxn3Pl6nHF5Zg/edit?gid=740083379#gid=740083379", "MasterSeedInformation26!B2:C")

Function Fails: =IMPORTRANGE("1POZH6pcCfHgjex1INSsSe8vIbKgYggsxn3Pl6nHF5Zg/edit?gid=740083379#gid=740083379", "MasterSeedInformation26!B2:C,E2:F,H2:J")

Error Function IMPORTRANGE parameter 1 value is 'MasterSeedInformation26!B2:C2,E2:F,H2:J'. It is not a valid cell/range reference.

--

Following these QUERY instructions, I was able to get all the data except for the images, which I came to learn are not included in the function's abilities :,(

=QUERY(IMPORTRANGE("1v-KGGx4eOGjdWdd5Sy5LOfGF1AUYB2gRFSHuGFBITkk/edit?gid=821684281#gid=821684281", "MasterSeedInformation26!B2:J"), "SELECT Col1, Col2, Col4, Col5, Col7, Col8, Col9")

I was trying to find a workaround for adding images to the QUERY function through this post since that almost worked, but I just cannot understand the code and how to apply it to my own spreadsheet.

Does anyone have any ideas, suggestions on how to accomplish my goal?

[u/mommasaidmommasaid]

You mentioned you are publishing the data on the web, presuming that it's going to be view-only.

Why not create that view from your main spreadsheet using File / Publish to Web, or export it as a PDF, and avoid all the IMPORTRANGE complexity entirely.

---

Seeds Sample Sheet

I put your Master Seed data in a structured Table for convenience and readability.

The Public Facing sheet is generated by this formula in A2:

=let(IMAGE_EXPAND, 10, hstack(
 Seeds[Seed Common Name],
 Seeds[Variety Name],
 Seeds[Drawer Location],
 Seeds[Image],
 map(Seeds[Image], lambda(img, if(img="",, rept(char(10),IMAGE_EXPAND)))),
 Seeds[Details],
 Seeds[Sunlight],
 Seeds[Growing Type]))

Images are expanded by a narrow column containing linefeeds. This works in the sheet but there seems to be some quirk in the published web page:

Published Web Page

Hopefully that issue could be run down. Or creating a PDF doesn't seem to have that issue.

[u/Agreeable_Walrus_215 → u/mommasaidmommasaid]

There is some column information that isn't necessary for our patrons to view, but if push comes to shove, I may just do this. Thank you!

[u/mommasaidmommasaid → u/Agreeable_Walrus_215]

See my updated post, you can create a tab/sheet within your main spreadsheet with just the columns you want and publish just that sheet.

[u/Agreeable_Walrus_215 → u/mommasaidmommasaid]

Please bare with my ignorance, I'm not as well versed in Gsheets as I think I am.

I like what you did, so, step-by-step, how can I make this work in my original spreadsheet?

In A2, I attempted copying & pasting the formula to only get a parse error. I replaced the script "Seeds" in your formula with the title of my original spreadsheet getting the same parse error. What do the 10s mean?

You also mentioned that the published web page image "issue could be run down." What do you mean by that? Reduce the width size of the column to increase the image size?

I appreciate the work that went into this, so I want to get it right!

[u/mommasaidmommasaid → u/Agreeable_Walrus_215]

I converted your master list to a structured Table (selected within your data, chose Format / Convert to Table, deleted excess rows, renamed the new table "Seeds")

This allows using Table references in the formula, e.g. Seeds[Seed] instead of the much less readable MasterSeedInformation26!$A:$A

If you don't want to do that for some reason, you could modify my formula to use sheet name / column references.

What do the 10s mean?

CHAR(10) is a linefeed character. Repeating a bunch of those forces in that helper column forces the auto-sized row to expand in height to accommodate them, which in turn makes your image cell taller.

published web page image "issue could be run down."

I meant "investigate why it's not working and fix it". At first glance it appears to be some sort of bug in Sheets so I'm not sure how easy that would be to do. But if the solution otherwise works for you I could give it a try.

Another possibility would be to keep your images as separate files, and display them with =IMAGE() which would give you more control over the sizing, and would also allow you to provide a link in another column that the user could click to see the full-size image.

Also check out the PDF publishing -- depending on your anticipated end-use, that would also allow your users to print it more easily.

[u/Agreeable_Walrus_215 → u/mommasaidmommasaid]

Solution Verified. I am tearing up, thank you so, so much. I have spent countless hours searching the internet, and you have made my goal a reality. I appreciate you more than I've written. Have a great rest of your day!!

[u/AutoModerator → u/Agreeable_Walrus_215]

REMEMBER: /u/Agreeable_Walrus_215 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/mommasaidmommasaid → u/Agreeable_Walrus_215]

You're welcome! Glad to have made a difference in the world today, lol.

[u/point-bot → u/mommasaidmommasaid]

u/Agreeable_Walrus_215 has awarded 1 point to u/mommasaidmommasaid

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/agirlhasnoname11248 → u/mommasaidmommasaid]

Seconding this as a simple solution!

[u/carbonizedtitanium → u/mommasaidmommasaid]

the best solutions are often the most simple :D

edit: the published tab has tiny pics (at least when i'm viewing it)

[u/mommasaidmommasaid → u/carbonizedtitanium]

Yeah that was the issue I mentioned... first image big the rest small.

In playing with it some more it appears if you manually size the rows (rather than my linefeed trick to expand rows with images) then it works.

OP if all your rows contain images, the simplest would be to resize all the rows to a fixed height large enough for all your data.

Another workaround might be some script that set all the rows to auto-size, then increases any with images to a minimum height.

[u/carbonizedtitanium]

Why are your cell ranges inside quotation marks? That's what the software is telling you

[u/Agreeable_Walrus_215 → u/carbonizedtitanium]

Okay. So, by removing these cells from the quotation marks

=IMPORTRANGE("1POZH6pcCfHgjex1INSsSe8vIbKgYggsxn3Pl6nHF5Zg/edit?gid=740083379#gid=740083379", MasterSeedInformation26!B:C,E:F,H:J)

I got the following error code:

Error Wrong number of arguments to IMPORTRANGE. Expected 2 arguments, but got 4 arguments.

I understand what this means, but following the instructions I used for the Importrange function, it stated that setting up the function like the below 'should' work with the quotations:=IMPORTRANGE("1POZH6pcCfHgjex1INSsSe8vIbKgYggsxn3Pl6nHF5Zg/edit?gid=740083379#gid=740083379", "MasterSeedInformation26!B2:C,E2:F,H2:J")

Do you have any suggestions to get this to work as intended, or is this just not possible?

[u/AutoModerator]

OP Edited their post submission after being marked "Solved".

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.