I need a solution for linking images

[anonymous]

We have thousands of images stored in our server, I can create a VB routine to export certain image folders to an online cloud storage at intervals throughout the day so the numbers would reduce.

I need to think outside the box and find a way for Appsheet to access the folders, has anyone been down this path before and lend some advice or recommendation on how to start, tips - tricks - anything??

Kind Regards

[GreenFlux]

Hi @Dave_Willett,

There are lots of things to consider here. Could you give us some more info on your data and requirements, please?

• Are all the images related to the same table?
• Are they currently in a nested folder structure, or do you want to organize them that way going forward?
• Are the images already named with the Parent-Record’s ID, or their own row’s ID?
• Do the images need to be accessible outside the app, with public URLs?

---

Here’s one option that may work for you:

[BULK IMAGE UPLOAD: Drag Files to Google Drive Folder and Auto-Add to AppSheet/Google Sheets](https://community.appsheet.com/t/bulk-image-upload-drag-files-to-google-drive-folder-and-auto-add-to-appsheet-google-sheets/27455) Tips & Tricks ?

IMAGE UPLOADS: Current Limitations in AppSheet Only one image can be uploaded at a time Filename is tied to key, and does not use file-name from uploaded file SOLUTION: Integromat Scenario; Google Drive → Sheets Integromat offers a free plan than can monitor a Google Drive folder and add new rows to Google Sheets. [Screen Shot 2020-07-09 at 9.57.40 PM] DISCLAIMER/FULL DISCLOSURE: GreenFlux is an AppSheet, and Integromat, Partner Developer. I am not asking for referrals, and use of this i…

---

[anonymous]

This looks amazing…

• Are all the images related to the same table?
  The table isn’t created as of yet, the images are in folder structures on our server for the time being. I haven’t moved them. The table should consist of a [Key], [JobID], [ImageURL]
  The structure of the files is:

  

Each folder is named after the [JobID] which is unique.

They would only need to be accessible by the App.

I hope this is what you meant.

Cheers

[anonymous]

Hi. Why do I keep getting the following: I have followed the instructions to the letter?

[GreenFlux]

Hmm… I haven’t seen that error before. I may have only gotten this to work because I already had the Google Drive API enabled on my account.

Try visiting the link suggested in the error. You might have to create a new project. I’ll test this from another account later this week and see if I can get it to work without the API enabled.

[shaaland]

Hi @Dave_Willett,

Are you primarily looking to just browse these files via an AppSheet app?
Can you tell me more about the use case?
What are these images for?
Who creates them and puts them in Drive?
Who would use them from AppSheet?
I assume the folder structure is dynamic (every new job will create a new folder). Can you confirm?
Would you want to also run these images through some automated process, like pass them through the Doc AI service from Google to recognize and categorize the images somehow? (ie: OCR for Receipts or Invoices)?

Use cases might be:

1. Search by Job ID 1234 to locate the folder for that job
2. Show me all the images for the Job ID 1234

• Would you want to view these images in a card or deck view where you can get a preview of the image
• Or, do you just want to look at the file names?

1. When new images arrive, notify AppSheet User X so that they can review the Job Images and approve the job completion?
2. When new images arrive, run them through the Doc AI service from Google AI to collect data from them automatically.

I’m totally guessing on the use cases, so please correct these.

Thanks,
Scott

[anonymous]

Hi Scott.
My App shows all vehicles within our workshop, each vehicle has a unique ID via JobID which can’t be duplicated. For each vehicle having a repair done there are a set of images take whilst in-progress.
So from your comments No 2 'Show me all the images for the JobID 1234.

Ideally for simplicity I would want a gallery, so it should be ‘Show me a gallery for images beginning with JobID 1234’

All images are in one Google Drive folder called \ImagePath I changed how the upload structure works so all images are in one location and each set of images are altogether. So there is not a separate folder for each JobID:

The images are uploaded once each day by an access database running in the workshop which is totally automated. The images are selected for upload via a csv file and that csv file should be the table for the gallery.
We don’t need any fancy bells and whistle such as Doc AI or Google AI, we simply just need to show a basic gallery. So if I select the row which contains JobID 244786, then allow me to see a gallery where the filename (before the “-” character) = JobID from the row.

Does this make any sense?
Cheers

[anonymous]

Is this possible guys?

[shaaland]

Hi @Dave_Willett,

Yes, this makes sense! I think putting all the files in one directory will help a lot to simplify how to navigate through the files. What I can suggest for now is to use an external process to put your file names and links to the files into a sheet. You could use Apps Script to do this and you could trigger the Apps Script to run at the end of your Access DB job that writes the files (or use an external tool as suggested by others above, or just modify your Access script?). One you have the file names in a sheet, you can use expressions and slices to get the views you want.

We do have an interesting feature on the roadmap that will make this easier in the future called the Google Drive Data Source. It will allow you to view a Drive folder as a Table, and each file as a row. Once that is available, you would not need an external process to get the view you want of the files in your app. Would you be interested in being an early adopter to try it out? If so, let me know.

All the best!
Scott

[GreenFlux]

Scott_Haaland:

We do have an interesting feature on the roadmap that will make this easier in the future called the Google Drive Data Source. It will allow you to view a Drive folder as a Table, and each file as a row. Once that is available, you would not need an external process to get the view you want of the files in your app.

Oh man, this would be awesome! Could you sign me up for the beta test please?

[anonymous]

Hi @Scott_Haaland, that would be such a fantastic addition to the suite of Appsheet, please sign me up.

Kind Regards

[Koichi_Tsuji]

Scott_Haaland:

We do have an interesting feature on the roadmap that will make this easier in the future called the Google Drive Data Source. It will allow you to view a Drive folder as a Table, and each file as a row. Once that is available, you would not need an external process to get the view you want of the files in your app. Would you be interested in being an early adopter to try it out? If so, let me know.

I almost overlook this important announcement! Better to push to new thread with other category rather than Quesion.

I wish to try this new feature as well

ID 473552

Im curious if this new feature enable us to access property of saved files, such as meta data, size, file ID (Google Drive assigned file ID), etc… Any quick list for the available property is there to share?

Thank you.

‍♂

[LeventK]

@Scott_Haaland
I’m highly interested…Please opt me in…
ID: 245151

[shaaland]

Hi @tsuji_koichi - san,

Yes, it will be announced more broadly once we are a little closer to being able to get a preview release ready. We will not be able to start the beta/preview until after the New Year. I’ve marked you and all the others in this thread down as interested beta testers, so I’ll make sure to let you all know when we are ready to start.

Yes, you will have access to some of the metadata. We will start with the most commonly used fields like File Name and it’s unique ID, File Size, DateTime Created and modified, etc… If you have any obscure properties that you think would be useful, it would be good to know about those, and what the use case would be for the additional properties. Each property would just be a column in your table.

Thanks for your interest!

[anonymous]

It can’t happen quick enough…

Cheers

[Koichi_Tsuji]

Hi @Scott_Haaland

Thank you for your response and explanation. I will looking foward to testing this new feature in due course.

I assume google Drive API could be involved somewhere in this new development. In that case, the properties which will be available under FILES object could be readable?

“imageMediaMetadata”: {
“width”: integer,
“height”: integer,
“rotation”: integer,
“location”: {
“latitude”: double,
“longitude”: double,
“altitude”: double
},
“time”: string,
“cameraMake”: string,
“cameraModel”: string,
“exposureTime”: float,
“aperture”: float,
“flashUsed”: boolean,
“focalLength”: float,
“isoSpeed”: integer,
“meteringMode”: string,
“sensor”: string,
“exposureMode”: string,
“colorSpace”: string,
“whiteBalance”: string,
“exposureBias”: float,
“maxApertureValue”: float,
“subjectDistance”: integer,
“lens”: string
},
“videoMediaMetadata”: {
“width”: integer,
“height”: integer,
“durationMillis”: long
},

We deal with bunch of “image” files under Appsheet app. So access image file meta data is super useful. However, there is downside… Currently, Appsheet is not retaining the meta data for image file once we upload image through the appsheet.
Along with this new feature development, I really wish your team to review the current limination to drop meta data of image file, expecially the datatime, lat/long and other valuable meta data could be retained when we up the image.

Arther is on a case to introduce new video upload feature as well. Once this happens, the access to videometadata could be also useful, as we can capture the duration and other meta data for the file and display withing appsheet app once uploaded.

[Get the Google Drive File IDs automatically and manupulate for bits and pieces](https://community.appsheet.com/t/get-the-google-drive-file-ids-automatically-and-manupulate-for-bits-and-pieces/34716) Tips & Tricks ?

Borrowing the complete code (Google App Script) from the web, like stackoverflow, such as The function below is going to list up the file names and file Id as well as file size for the target folder. function list_all_files_inside_one_folder_without_subfolders(){ var sh = SpreadsheetApp.getActiveSheet(); var folder = DriveApp.getFolderById(‘ChangeHereToYourFileFolderId’); var list = ; list.push([‘Name’,‘ID’,‘Size’]); var files = folder.getFiles(); while (files.hasNext()){ file = fil…

This is a trick I m currently using to capture Google Drive file IDs, this process is painful, as I dont want to CODE as much as possible. Once we could capture Google Drive file ID automatically or rather simply than pushing own GAS, then it will be another game changer. We can create the download link for the file using IDs and can do more by manipulating the ids.

Thank you and really looking forward to having it.

Koichi

–

Maybe it is a good idea to get the path & folder ID for the files as well.

@LeventK

[LeventK]

Absolutely @tsuji_koichi san

[shaaland]

Hi @tsuji_koichi - san,

Thanks for your valuable input. I’ll take this into consideration…we want the table to be as simple as possible, but if dealing with images primarily, I can see how all this meta data would be useful.

Thanks,
Scott

[anonymous]

Well I have this working, but it is very slow. Google script:

*/
function listFilesInFolder(folderName) {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.clear();
sheet.appendRow([“Name”, “Date”, “Size”, “URL”, “Download”, “Description”, “Image”]);

//change the folder ID below to reflect your folder’s ID (look in the URL when you’re in your folder)
var folder = DriveApp.getFolderById(“Google Folder ID Goes Here”);
var contents = folder.getFiles();
var cnt = 0;
var file;
while (contents.hasNext()) {
var file = contents.next();
cnt++;
data = [
file.getName(),
file.getDateCreated(),
file.getSize(),
file.getUrl(),
“https://docs.google.com/uc?export=download&confirm=no_antivirus&id=” + file.getId(),
file.getDescription(),
“=image(“https://docs.google.com/uc?export=download&id=” + file.getId() +”“)”,
];
sheet.appendRow(data);
};
};

Which populates a column in your Google Sheet (used as the table)

But it is painfully slow on loading the image.

[GreenFlux]

Well done, @Dave_Willett! I’ll bet that was fun to figure out!
I’ve built a few import tools like this and it’s always a fun learning experience.

On your post, try enclosing the code with 3 tic-marks ``` on their own line, at the beginning and end.

That will separate the code with a scroll box like this:

*/
function listFilesInFolder(folderName) {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.clear();
sheet.appendRow([“Name”, “Date”, “Size”, “URL”, “Download”, “Description”, “Image”]);
...
...

---

And I can’t tell for sure, but it looks like you’re updating the sheet once for each row. That could be why it’s so slow. Try building the array first, and then writing all the data to the sheet in one edit.