No server for media, no problem

Kirk_Masden · 01-07-2024 01:54 AM

Postscript: Since writing this post, I have learned that the "tricks" described here will soon stop working. For more information please see the following:
Preparing for third-party cookie phase out
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Preparing-for-third-party-cookie-phase-out/td-p/...

I have found that OpenDrive is an easy-to-use solution that allows one to store both image files and mp3 files on the web and then use their urls to access them in an app as explained below.

In AppSheet, images can be displayed in image columns, which allow uploading of files.

However, there may be instances when you might like to store (host) media files on a server so that they can be accessed via the HTML that is allowed in "long text" columns. Also, if you would like to link to mp3 audio files or mp4 video files so that users can listen or view or them via their devices browser, you need to be able to host them on an external server; AppSheet does not yet support audio and video files in a way that is analogous to how they support image files so some sort of hosting strategy will be required.

I happen to have access to my own server but, until recently I did not realize that I could use Google Drive to place files on the web. Here's the procedure:

Upload file (image, animated gif or audio) to Google Drive
Set "Share" to "Anyone with link"
Select "Copy link"
Paste link into a text editor to extract the file ID (number and letters) which is located where you see [file_ID] in the following URL: "https://drive.google.com/file/d/[file_ID]//view?usp=sharing"
Put the file ID you just extracted into the following URL where you see [file_ID]:
https://drive.google.com/uc?id=[file_ID]

Here are two media files you can use to test this out.

Mona Lisa (image)
https://drive.google.com/uc?id=14fpsJkz8jBz6Je_uHcxonnqsCK3RBCK4

Good job! (audio)
https://drive.google.com/file/d/1hlg5woBc4JpeNx0jDnTO0jQtPzkK7aU_/view

Here's how they are referenced in the spreadsheet of my app.

Note: I've changed the mp3 URL because the one in my original post ceased to work. I now use https://drive.google.com/file/d/1hlg5woBc4JpeNx0jDnTO0jQtPzkK7aU_/view. This URL forces to user to tap the play button again in the browser but it seems to work.

Let's look at the image first.

This is how it appears in a view in which a question is posed to the user (my app is a flashcard app for language learners and other students).

The width of the image is 350 pixels, which makes it display well on all of the devices I have tested it on. Image files on AppSheet are normally restricted to landscape mode (horizontal) but the display of images via HTML makes it possible to display via portrait mode (vertical), as you see in this case. This is another reason why independent hosting can be desirable. Note that images in HTML are not resized automatically so you need to make sure that your images are of the appropriate size (350 pixels or less in width) to avoid problems.

Now for the audio. In my app, the "PLAY" button toward the top links to the audio file, which will play in the device's browser.

Finally, this little tip of mine is related to other tips that have already been made. Here are some of them:

Host KML files on Google Drive
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Host-KML-files-on-Google-Drive/m-p/335162

Get the Google Drive File IDs automatically and manupulate for bits and pieces
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Get-the-Google-Drive-File-IDs-automatically-and-...

There are other threads that may be related but, as far as I can see, all are slightly different from what I have posted today.

Happy creating!

Suvrutt_Gurjar

Hi @Kirk_Masden ,

Thank you very much for a nice tip. The use of HTML long text column really opens many avenues for displaying images more intuitively.

About the following part of using File ID of the image or other files from G drive to display those files in the AppSheet app used to be covered in AppSheet help documentation. However of late this approach of using "File ID" with URL to display a file in AppSheet app is removed from documentation and AppSheet documentation team has given the following clarification as mentioned in the post I have referred below. However it sounds that the approach still works.

@Kirk_Masden wrote:

I happen to have access to my own server but, until recently I did not realize that I could use Google Drive to place files on the web. Here's the procedure:

Upload file (image, animated gif or audio) to Google Drive

Set "Share" to "Anyone with link"

Select "Copy link"

Paste link into a text editor to extract the file ID (number and letters) which is located where you see [file_ID] in the following URL: "https://drive.google.com/file/d/[file_ID]//view?usp=sharing"

Put the file ID you just extracted into the following URL where you see [file_ID]:
https://drive.google.com/uc?id=[file_ID]

Please refer my query post on this topic a few months ago and the response by the AppSheet team.

Solved: Displaying an image file from Google drive by fil... - Google Cloud Community

Kirk_Masden

Thanks! I tried to look for relevant threads but missed yours, unfortunately. Sorry.

Suvrutt_Gurjar

Hi @Kirk_Masden ,

No worries. Please do not be sorry. You have so many valuable tips in the community. We cannot search each and every post in the community and occasional unintentional overlaps of posts is natural and happens with us all.

My main intention behind sharing this recent earlier post on the topic was that displaying images/files from G drive using file ID seems to be getting deprecated.

The alternative approach mentioned now in the help documents (image below) does not seem to offer a way to access images in any folder in the G drive. The recommended way is to place the images/files in the same folder where spreadsheet of the app is located and then use relative path.

Kirk_Masden

Thanks your information and kind words! I recognize that a day may come when my method won't work any more. My idea, however, is a way to overcome the limitations of the "File name value" method in the context of Long text columns that are set to HTML rich text and with links to audio and video files on the web. I just tested it and relative URLs (file name values) that work inside an AppSheet image column do not seem to work inside Long text columns set to HTML rich text.

To clarify, the method I've tried to outline here is how to add images to Long text columns via HTML (rich text) and how to add links to video and audio that play in the devices' browsers. The advantage of adding images in Long text columns is that one can add multiple images without increasing the number of image columns. In my flashcard app, most "cards" do not require more than one image (many don't need an image at all) but, rarely, some cards can benefit from the addition of multiple images. I've been adding additional images via a Long text column to circumvent the need to add additional images columns that are rarely used.

Also, at present, the only way I know to link to mp3s and mp4s is with absolute URLs. If one doesn't have access to a server, I suspect that the method I've outlined may be the best free method at present.

But, as you indicate, the deprecation of this method means that people should use it with the understanding that it may cease to work someday.

Kirk_Masden

Oops!! Speaking of being deprecated, my original audio URL, which worked when I first posed, no longer seems to work. Images are still OK but user beware!

Suvrutt_Gurjar

Hi @Kirk_Masden ,

Thank you for the neat perspective. Appreciate.

As we write, there is another thread going on about displaying external images through URL in AppSheet.

Nice part is AppSheet dev team have mentioned that they are actively looking into it.

Displaying External URL Images in AppSheet Suddenl... - Google Cloud Community

Kirk_Masden

Thanks to your alerting me to the issue, @Suvrutt_Gurjar , I think I have been able to come to grips with the coming end to third-party cookie support. If you have time, please read my "tip" about the issue and let me know if I've gotten anything wrong:
Preparing for third-party cookie phase out
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Preparing-for-third-party-cookie-phase-out/td-p/...

Suvrutt_Gurjar

Hi @Kirk_Masden ,

Thank you for your elaborate post on preparing for the upcoming change in serving images in an AppSheet app. Your tips post will be very useful for the community. I believe your tips in that post are in the right direction. I have not tested each and every option at this point but I am sure you have and your insights are always thorough. I will definitely post if I come across any findings worth the community attention.

It will be great if AppSheet development or documentation team also comes up with an announcement post with details on the lines of "What is changing", 'What is not changing" and so on related to this "Image serving in an AppSheet app change". I believe they would do so in due course of time.

Kirk_Masden

Thanks so much!!