It has been really difficult to figure out, but I got there.
Populating the sheet is not too bad as it happens once every hour and there are (now) usually only 500 or so images. But the slow issue is that the column I take the image from is still a URL:
So Appsheet has to try to change that to an Image. I’m sure the guys at Appsheet will soon have an update so we can just load images direct from Google Drive.
Question: Can an array be built in Appsheet before the URL is converted to an image?
Dave_Willett:
Question: Can an array be built in Appsheet before the URL is converted to an image?
I was referring to building an array in your Apps Script code. There’s no equivalent in AppSheet.
Instead of this:
sheet.appendRow(data);
-try storing a variable of the row instead of writing it directly to the sheet. Then push (append) the data of each new row to the variable to build up the full set of row before writing it to the sheet.
I wouldn’t know where to start to be fair. I’m open to any assistance I can get with this.
Thank you for the advice though.
Kind Regards
Good Job @Dave_Willett! This is a great accomplishment you can be proud of.
It does seem that the issue is not in the loading of the sheet row by row (the Apps Script), but rather the App User looking at the images from the sheet (which should already be pre-constructed by the Apps Script on an hourly basis).
So, assuming you have a sheet with URLs pointing to images on Drive…we need to figure out why the app load of those images is slow.
According to this help article, the URL should have export=view instead of export=download, and the column should be of type “Image” in the table definition.
So your URL: https://docs.google.com/uc?export=download&confirm=no_antivirus&id=” + file.getId() construction statement should change to:
https://drive.google.com/uc?export=view&id= + file.getID()
I suspect that the Download directive is what is taking the extra time, but I’m not sure. Could you try changing a few of the image URLs manually in your sheet and see if they load faster? If so, you can just change your Apps Script and re-run it. Please let us know what you find out.
R,
Scott
This is now getting exciting, let me see what I can do with your suggestion.
Much appreciated.
Ok so I’ve changed the URL in the script (Removed some characters from the folder ID for security):
If I have understood the recommendation, does this look correct to you guys?
The script returns this in the Google Sheet (Notice the column [Download] and [Image] both have data):
This is the structure of the table in Appsheet:
So if I look at the table data in Appsheet notice what happens in the [Image] column, it is empty but the [Download] column still has the URL. The column [Image] also returns nothing in a view:
Changing both URL’s to:
Does allow the Gallery and Images to load slightly faster though…
@Scott_Haaland please sign me up for the test.
Update
As my images are exported via a workshop TV (linked to a PC) into the local (C:\users\dave.willett\Google Drive\ImagePath…) via Microsoft Access, I am able to utilise the WIA - Windows Image Acquisition Library to reduce the imge size before it gets to the Google Drive and therefore a 737kb file is now approx 45kb and they load much quicker…
I did have a recent project where my clients save thousands of image and movie files directly down to the google drive (files not being saved from Appsheet App) initially, and then we display those images and movie file on the Appsheet App.
Firstly what I did was similar to yours, let the google app script to get the full path for those files, where the URL looks like https://drive.google.com/uc?xxxxx. I concat the strings of https along wiht the Google drive file ID to manually construct the public URL. Then I found URL itself is working fine, but it takes almost forever to render images on the Appsheet app. Just deadly slow.
My observasation was it is natually slow, as we query to Google s server to get the files. File IDs would give us a key to access to target file, but it was making sense it takes a bit of time to get the response back from Google server to render the target images, as query strings (google file ID) should search ALL the files stored witin the server. Meaning, query strings are checking ENTIRE google drive server to find a single file. It should be by design, so I wont complain.
Then I altered my approach a bit. I get the google apps script which will yeilds the FULL PATH for all those image and movie file. We need to run script manually to get the data donw to the spreadsheet, which is fine with me. No automation is needed on my use case.
After playing around a bit for strings, eventually I managed to get the strings which stands for the relative path + file name of each file as strings. This can be done within Appsheet Virtual column where we dynamically construct Appsheet Style of file path, meaning the folder name(s)/fine name.
Then set this vircual column as Image type.
Appsheet render images acceptably fast now. This could be because the relative strings give the shortest way and quickest access to the files stored my own google account drive, rather than checking any other folders owned by others. When we use the public url of https://drive.google.com/uc?xxxxx fileIDs… then naturally we are querying to other google drive account hold and owned by others.
Bit of tricks to manage this challenge.
Lesson learned from this use case.
Yes, public URL of https://drive.google.com/uc?xxxxx. fileID can be used within Appsheet app, but in case the amount of image files to deal with is small enough.
Once the image file are large or huge, then it takes almost forever to render image through this URL, then should swith to the “relative path” to construct the URL and render to Appsheet.
@Scott_Haaland
I would suggest as well as hope that we could access to each files RELATIVE PATH strings from new features should be useful based on my recent experience which mentioned here.
https://drive.google.com/uc?export=view&id= + file.getID()
would work as far as we are able to access to fileID value, but it becomes inactive as the number of the files glows to render on Appsheet.
Better and alternative approach is to get the files relative path, starting from root folder PLUS file name. Then we can construct relative path for each images as a string. This relative path (manually and virtually constructed) can be used as if the files are saved from Appsheet app.
Hope this makes sense with you as well.
Hi, thank you for that.
Having tested my results over the last few days and even compressing my images to approx 25kb ( with loss of quality ) the rendering is still very slow, so really I didn’t necessarily have to compress them down too much.
I am at a loss with this and it is very important to fix this.
Any help I can receive to speed up the rendering would be greatly appreciated.
Regards
Sorry for confusion if I made, but using my tricks should be nothing to do with file size.
My suggestion is not to use
https://drive.google.com/uc?export=view&id= + file.getID()
type of public URL in case you have bunch of image files to deal with.
As it should render images within Appsheet deadly slow.
Do you have script which yield file names on the spreadsheet?
Are image files sitting in a single folder within your drive?
If your answers for both are positive, you are around the corner.
Let me assume you have spreadsheet with column with file name. [fileName]
Then if your image folder are sitting on the root position with folder name of “Images”, then you construct the virtcual column with expression something like
Concatenate(“/images/”,[fileName])
which returns the relative path (in Appsheet style) dynamically. Set this virtual column as image type.
Hope this is clear to you.
I just reviewed your code and looks your have file.getName() methods already as well as you have single folder to get those. So my suggestion naturally works.
Good luck.
I’m just testing it. I never thought the virtual column method works, but it does.
Obviously when Appsheet introduce the Google Drive Table it will be extremely valuable to many users.
Thank you for your assistance.
Yes, once they introduce goole drive file table features, hopefully we say good-bye to script.
Good to hear you seem to solve the problem, and app should now run faster to render image.
Yes there is an improvement, thank you again.
Thanks @tsuji_koichi - san,
This is good information to know. In any case, with the Drive data source, it will only point to a single folder at a time, so the relative path should be a simple formula to add the known path (the folder that is currently configured) and the file.name(). I’ll raise this with engineering and suggest that we run some volume tests to try this out. I’m surprised that fileId is not an index in Drive that should be really fast compared to file names!! But based on your experience, it looks like there is some other design in place!
We’ll look into it and get to the bottom of this.
Thanks again, and Happy New Year!
Scott
Happy new year once again @Scott_Haaland
Yes, appsheet’s “relative path” to the target file should be the most shortest way to reach out to files rather than Google drive file ID based URL. I m not checking from technical aspects, but it should make sense.
Google drive “DriveApp” Class is providing the methods to get the folders name where the target files are sitting in. So I believe your engineering team can relatively easily add this property to the new table feature.
I could not loacate your previous post, but I remember the new feature yet able to get the nested folders and tree structure to get the meta data. However, as you can imagine, the most of the practical use case of the drives are using tree structured folders system to store the required data. I hope the application to the tree folder system to get meta data even from the child folders is on the scope for the further development.
Thank you.
Hi Tsuji, happy new year and thank you for the work you’ve done here.
I’m Mike, I’m new on the AppSheet team but have been at Google for a while now, and used to work on the Drive API (Google Developers Blog: Announcing Version 2 of the Google Drive SDK).
This is a really interesting thread. I understand we’re using Drive as a datasource and experiencing slowness when rendering the images in this case. Also understood that you get a performance increase when using a different approach for querying the files (is this a different library altogether?)
Actually getting file metadata by fileId should be lightning fast. FileID is in fact a universal index and is, historically, the fastest way to retrieve file metadata. This of course assumes that querying by fileID is using the specific files.get() call in the underlying API, and not a generic files.list() command with ‘q’ parameter query string and passing in file ID or something like that to the generic files.list() command. We’ll want to confirm that on our end.
Two things to try:
You may be able to speed up the results of a get request, by explictly using the “fields” parameter to request only the fields needed when using the API:
Return specific fields for a file | Google Drive API
There is an alternate download mechanism for drive other than the /uc endpoint that, if you’re just trying to get the bytes of the file, could be faster. They are referred to as ‘media’ downloads and, in my experience, I would consider this the canonical way to retrieve binary file bytes. See here:
See how that does and let us know!
