Uploading images from app into google sheets

[MAGG_Group_Engi]

Hi,

I’ve created a App from an existing google form and it appears to be working great with the exception of the photo upload. The app is a reporting app for field engineers and they are required to take a photo for each submission but I cannot find a way to get the link or picture into my google sheet so it can be opened directly from the sheet. I need a hyperlink to the exact photo and an embedded copy ideally. All I can do at the moment is get the images to upload to a folder in my google drive but locating the correct one afterwards is a nightmare for the office.

Thank you in advance

[Rafael_ANEIC-PY]

Hello @MAGG_Group_Engineers , I know a solution for this but it doesn’t involve AppSheet, you can create a gscript that looks into your google drive folder for the pictures using their names, and then extracts their links and pastes them into your spreadsheet, are you familiar with using google scripts?

[MAGG_Group_Engi]

Hi Rafael_ANEIC-PY,
Thanks for your quick reply. I’m kind of familiar with it but by no means an expert. Happy to try anything though.
Thanks

[Rafael_ANEIC-PY]

I’ll try to set something up and post it here when i get it to work, gotta test it with my stuff first.

[Marc_Dillon]

MAGG_Group_Engineers:

I need a hyperlink to the exact photo

The following article details how to create a publicly available external URL to any image/file.

help.appsheet.com

Displaying Images and Documents

How to display documents and images in your app.

[Rafael_ANEIC-PY]

I got it to work !, here’s the code:

function getLinks() {
const folderID ="1ABCGkLB9QqNccdASLwdf_rdffRzTUp_G"; // This is the ID of the folder where the images are
const sheetName = "Images" // This is the name of the sheet that contains the image names
const linkColumnNumber= 2; // This is the number of the column that's going to contain the link to the images, it is counted starting from 0 at column A (example, column C would be number 2)
const nameColumnNumber= 1; // This is the number of the column that's going to contain the exact name of the images (including extensions like .pdf), counted the same way as above

const pdfFolder= DriveApp.getFolderById(folderID); 
const currentSheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); 
var range = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,currentSheet.getLastColumn());
var value = range.getValues();

for(var x=0;x<value.length;x++){
   if(!value[x][linkColumnNumber]){ // This checks that the link column is empty before searching for any links, so it doesn't waste time overwriting links
      var dupFileArray=[];
      var link = currentSheet.getRange(x+2,linkColumnNumber+1);
      var name=currentSheet.getRange(x+2,nameColumnNumber+1); 
      var files = DriveApp.searchFiles("title contains '" + value[x][nameColumnNumber] + "'");
      while(files.hasNext()){
         var file = files.next();
         dupFileArray.push("https://drive.google.com/file/d/"+file.getId());
      }
     link.setValue(dupFileArray[0]);
  }
}
}

It’s fully annotated and tested, let me know if you have any questions.

It needs a column that contains the exact image names (like the ones AppSheet provides, but without the /attachments/ part), the folder ID, the sheet name, and an empty column to save the links to.

It uses those to search the folder based on the name of each image, gets its link and saves it to the link column, if there’s already something saved on that column, it skips the row, so it doesn’t waste time overwriting links.

You could set it up to run on a timer or manually using a button, that’s up to you.

PD: Adding the final code for future queries:

function getLinks() {
const folderID ="folderID"; // This is the ID of the folder where the images are
const sheetName = "Form responses 1" // This is the name of the sheet that contains the image names
const linkColumnNumber= 28; // This is the number of the column that's going to contain the link to the images, it is counted starting from 0 at column A (example, column C would be number 2)
const nameColumnNumber= 32; // This is the number of the column that's going to contain the exact name of the images (including extensions like .pdf), counted the same way as above

const pdfFolder= DriveApp.getFolderById(folderID); 
const currentSheet= SpreadsheetApp.getActive().getSheetByName("Form responses 1");
var range = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,currentSheet.getLastColumn());
var value = range.getValues();

for(var x=0;x<value.length;x++){
   if(!value[x][linkColumnNumber] && currentSheet.getRange(x+2,linkColumnNumber).isBlank()==false){ // This checks that the link column is empty before searching for any links, so it doesn't waste time overwriting links
      var dupFileArray=[];
      var link = currentSheet.getRange(x+2,linkColumnNumber+1);
      var name=currentSheet.getRange(x+2,nameColumnNumber+1); 
      var files = DriveApp.searchFiles("title contains '" + value[x][nameColumnNumber] + "'");
      while(files.hasNext()){
         var file = files.next();
         dupFileArray.push("https://drive.google.com/file/d/"+file.getId());
      }
     link.setValue(dupFileArray[0]);
     SpreadsheetApp.flush();
  }
}
}

[Rafael_ANEIC-PY]

Could you give me edit permissions to both to my email rafaalmeida@fiuna.edu.py please? so i can check what’s up

[MAGG_Group_Engi]

Done.

[Rafael_ANEIC-PY]

Did some modifications to the script, added a check in case there’s no name on the image name column so it doesn’t run on rows where there are no images, added an extra column to the right that extracts the exact image name, for example:

Form responses 1_Images/ABCDEFG.jpg is not the image name

ABCDEFG.jpg is

And i get that using this formula: =if(AB2<>"",RIGHT(AB2,LEN(AB2)-24),)

24 is just the length of that starting string.

Give it a run when you can, it runs correctly for me.

[MAGG_Group_Engi]

Hi,

That is brilliant ! Thank you.
So I shouldn’t need to change anything moving forward? If I open the spreadsheet on a daily basis and click run it should extract all the links for any future rows?

[Rafael_ANEIC-PY · Accepted solution]

No problem, glad it works for you.

MAGG_Group_Engineers:

So I shouldn’t need to change anything moving forward?

Nope, just remember to drag down the formula in the column I added when you eventually add new rows to the sheet.

MAGG_Group_Engineers:

If I open the spreadsheet on a daily basis and click run it should extract all the links for any future rows?

Yep

[Koichi_Tsuji]

Possibly this can be achieved in Appsheet way , no coding , by using read single folder as table.

[MAGG_Group_Engi]

I’m sorry but I must have missed this comment! Could you explain more ?

[MAGG_Group_Engi]

I have a new form which populates a separate table in my app and I need the same process to happen. So the appsheet form creates a image file but the url does not appear in my sheet. I’ve tried using the code you help me with before but I cannot get it to work. There is 2 images in this sheet that need the URLs.

docs.google.com

Fire Stopping Form (Responses)

Form responses 1 Timestamp,Address,Point ID on plan,Floor level,Site Plan,Site Plan URL,Photograph ,Photograph URL,Substrate that requires fire stopping,Required fire rating,Fire stop system required,Timescale to complete,Number of engineers...

[Rafael_ANEIC-PY]

Hello @MAGG_Group_Engineers, you only need to replace the folderID, sheetName, and the two column numbers for the name and the link of the files, i’ve added the final code to my reply, you can use it as a guide to adapt it to your new sheet, you can ask me any specific questions about it if you’d like.

You could duplicate the function and run it once for each column, or just duplicate the code inside the same function for getting the links to both files.

Rafael_ANEIC-PY:

PD: Adding the final code for future queries:

function getLinks() {
const folderID ="folderID"; // This is the ID of the folder where the images are
const sheetName = "Form responses 1" // This is the name of the sheet that contains the image names
const linkColumnNumber= 28; // This is the number of the column that's going to contain the link to the images, it is counted starting from 0 at column A (example, column C would be number 2)
const nameColumnNumber= 32; // This is the number of the column that's going to contain the exact name of the images (including extensions like .pdf), counted the same way as above

const pdfFolder= DriveApp.getFolderById(folderID); 
const currentSheet= SpreadsheetApp.getActive().getSheetByName("Form responses 1");
var range = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,currentSheet.getLastColumn());
var value = range.getValues();

for(var x=0;x<value.length;x++){
   if(!value[x][linkColumnNumber] && currentSheet.getRange(x+2,linkColumnNumber).isBlank()==false){ // This checks that the link column is empty before searching for any links, so it doesn't waste time overwriting links
      var dupFileArray=[];
      var link = currentSheet.getRange(x+2,linkColumnNumber+1);
      var name=currentSheet.getRange(x+2,nameColumnNumber+1); 
      var files = DriveApp.searchFiles("title contains '" + value[x][nameColumnNumber] + "'");
      while(files.hasNext()){
         var file = files.next();
         dupFileArray.push("https://drive.google.com/file/d/"+file.getId());
      }
     link.setValue(dupFileArray[0]);
     SpreadsheetApp.flush();
  }
}
}

[DondP]

Rafael_ANEIC-PY, any chance you can assist my with this script on my Google Sheet please?