Creating Google Image link

Chriss · 12-09-2019 12:23 AM

Hi,

Posting this again since I still could’nt figure out how to get google links for pictures uploaded by the app users.

Currently I have to manually run a google script (see youtube below), just to list all the pictures file name (abc.jpg), as well as shareable links within the image folder (https://drive.google.com/a/… I then lookup the file name, with the links generated from the script (very manual) to manually compare submissions …

I’ve tried several examples shared, but still could’nt figure out the %20 space problem (column name have space), and %20 cant work.

Would really appreciate if someone can help me solve this

Sergio_Sa

Hi @Chriss, you don’t need to list all files.
If you create a custom name to the image and save that name in the source sheet, you can run a script on change to get the picture name and set the Url in other column. For me works.
You can get the name of picture when uploaded? you have many uploads?

Chriss

Hi Sergio,

thanks for replying…

I don’t know how to create a custom name to the image. I just follow what appsheet provides. Running a script means setting a virtual column with some “formula” right? Can you share your formula? Does it include encodeurl?

Appsheet currently stores, in the image column within google sheet, the file name: say abc.jpg.

I have quite a few uploads from various apps…

Koichi_Tsuji

I read your movie but not sure what your problem is.

Kirk_Masden

Hi @Chriss!

Please forgive me if I’ve failed to understand your question properly but I wonder if this might not be the answer:

=CONCATENATE(“https://www.appsheet.com/template/gettablefileurl?appName=”, ENCODEURL(“AppName-Account#”), “&tableName=”, ENCODEURL(“TableName”), “&fileName=”, ENCODEURL(+ImageColumnCell))

Sergio_Sa

You probably need to read this:
https://developers.google.com/apps-script/overview#your_first_script

LeventK

@Chriss
You need to write down your own Google Apps Script for this

LeventK

@Chriss
Something like this might work better. Please bear in mind that I have no idea about your gSheet structure

function getImageShareableURLs() {
  var sSht = SpreadsheetApp.openById("Your_gSheet_File_ID_here");
  var sht = sSht.getSheetByName("Your_tabname_where_app_images_recorded");
  var data = sht.getDataRange().getValues();
  var folders = DriveApp.getFolders();
  var folderInfo = [];
  
  while (folders.hasNext()) {
    var folder = folders.next();
    folderInfo.push({fldName: folder.getName(), fldId: folder.getId()});
  }
  
  folderInfo = folderInfo.sort(function(a, b){
    var aName = a.fldName.toUpperCase();
    var bName = b.fldName.toUpperCase();
        
    return aName.localeCompare(bName);
   });
  
  for (var i = 1; i < data.length; i++) {
    var imgFile = data[i][5].toString();
    //[5] above represents the array index of your image column
    //where array index for ColumnA is 0
    imgFile = imgFile.split("/");
    var imgFld = imgFile[0];
    var imgNm = imgFile[1];
    
    folderInfo.forEach(function(fld){
      var foldername = fld.fldName;
      var folderId = fld.fldId;
      if (foldername === imgFld) {
        var imgInfo = [];
        var images = DriveApp.getFolderById(folderId).getFiles();
        
        while (images.hasNext()) {
          var image = images.next();
          imgInfo.push({imgName: image.getName(), imgId: image.getId()});
        }
        
        imgInfo = imgInfo.sort(function(a, b){
          var aName = a.imgName.toUpperCase();
          var bName = b.imgName.toUpperCase();
          
          return aName.localeCompare(bName);
        });
        
        imgInfo.forEach(function(img) {
          var name = img.imgName;
          var id = img.imgId;
          if (name === imgNm) {
            DriveApp.getFileById(id).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
            sht.getRange(i + 1, 5).setValue("http://drive.google.com/uc?export=view&id=" + id);
            //The index number 5 above represents Column E, Column A being index 1
          }
        });
      }
    });
  }
}

Chriss

Hi Levent,

Thanks for writing this code. i’m currently running it (taking a while)…
The image column is in column AA, so i’m guessing its data[i][26].toString(); as well as sht.getRange(i+1,27).setValue(“http:…”)

is this right?

Also, I’ve previously tried encodeurl by Kirk_Masden but i dont know why its not working for me. It has something to do with underscrolls “_” and spaces " ", giving me “file not found” results.

Sergio_Sa

Chriss

i use 26 for array index AA since column A is 0,
as well as 27 for sht.getRange (since column A is considered 1)…

but face this error

LeventK

@Chriss
Line 20 of the script is mal-formed…Change it like this:
for (var i = 1; i < data.length; i++) {

Chriss

So that other people can see / replicate, i also tried to use the following script (found online )

open blank new spreadsheet.
run following script -> (replace folder id) & (var names= without the space in between the square brackets)

function myFunction() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var s=ss.getActiveSheet();
var c=s.getActiveCell();
var fldr=DriveApp.getFolderById(“Folder-ID”);
var files=fldr.getFiles();
var names=,f,str;
while (files.hasNext()) {
f=files.next();
names.push([f.getUrl(), f.getName()]);
}
s.getRange(c.getRow(),c.getColumn(),names.length, 2).setValues(names); }

found here: https://support.google.com/docs/thread/16362151?hl=en

LeventK

Chriss:

function myFunction() {
	var ss=SpreadsheetApp.getActiveSpreadsheet();
	var s=ss.getActiveSheet();
	var c=s.getActiveCell();
	var fldr=DriveApp.getFolderById(“Folder-ID”);
	var files=fldr.getFiles();
	var names=,f,str;
	
	while (files.hasNext()) {
		f=files.next();
		names.push([f.getUrl(), f.getName()]);
	}
	
	s.getRange(c.getRow(),c.getColumn(),names.length, 2).setValues(names);
}

@Chriss
The code you have provided only retrieves all the file names and their owner accessible URLs under the designated Folder-ID to the gSheet. It does not match any image records/names existing on the gSheet. Actually the code is not so related with your case basically.

Chriss

Yea. I think your solution filters the folder based on file name, and then replace it with export=view links…
the other workaround i found online just list owner accessible urls of the whole folder.

For your solution, is there any workaround to replace export=view links with file name again. I didn’t expect it to be replaced…

LeventK

Can you explain what do you mean with that?

Sergio_Sa

My code is much more modest than @LeventK but I use to get the url on each submission. I add 2 actions for when the application updates the url already appears.
The script run on change.
My litle code:

    function alteracao(e) {
      var r = e.source.getActiveRange(),
          columnOfCellEdited = r.getColumn();
      if (
        e.source.getSheetName() == "url" && //name of AppSheet data source
        columnOfCellEdited == 1
      ) {
        url();
      }
    }
    function url() {
      
      Utilities.sleep(500);
      var source_folder= DriveApp.getFolderById('1q4pFhHgRfsmHB_bxdTFGnM84feVa6l8e'),
          
          ss = SpreadsheetApp.getActiveSpreadsheet(),
          sh = ss.getSheetByName('url'),
          rown = sh.getLastRow(),
          str = sh.getRange(rown,2).getValue(),//column of image 
          s = str.slice(str.search("/")+1, str.length);
      Logger.log(s)
      
      var num2 =1;
      var stop = 0
      do {
        var filesIMG = source_folder.getFilesByName(s);
        Logger.log('num2 '+ num2);
        if (filesIMG.hasNext() ){
          stop = 1
       
        var fileIMG = filesIMG.next();
        var idIMG = fileIMG.getId();
        var urlIMG = 'https://drive.google.com/uc?export=view&id=' + idIMG
        Logger.log(fileIMG)
         }
        //Utilities.sleep(500);
        num2++;
      }while (stop < 1);
      
      sh.getRange(rown,3).setValue(urlIMG); //column url
    }

LeventK

@Sergio_Sa
This is a good script, but;
1.) It will be triggered everytime when AppSheet changes a table/sheet regardless of the image is associated with. Therefore, provided a specific cell in a particular sheet is not a concern, I generally have the tendency to check the sheet name as well i.e.

function myFunction() {
	try {
		var sSht = e.source;
		var sht = sSht.getActiveSheet
		var shtName = sht.getName();
		
		if (shtName === "Some sheet name) {
			//some code block here
		}
	} catch (e) {
		throw (e)
	}
}

2.) In your example, you refer to a definite folder, get the files to an iterator and then loop thru each image with do…while loop. This will increase the execution time of the script provided you have a lot of images under the folder. Instead, I use a 2D array and loop inside the array prototype via forEach property which is significantly faster. If your account is a basic consumer account, the allowed runtime for a script is max. 6mins, and you can quickly hit this runtime quota.

3.) Instead of opening, reading and writing the gSheet object everytime, I prefer collecting the looped values to an array (1D, 2D, 3D whatsoever it may be) and then write the array content to a definite sheet range all at once which is significantly less time consuming.

Lastly, I may advise not using Utilities.sleep(n) code unless it’s intentionally necessary i.e. recording the values to CacheService, PropertyService etc. for later use in the script as a dynamic/universal content/variable.

Sergio_Sa

Thanks @LeventK , your script is far more complete than mine, alias as surely your knowledge in Apps Script. I am a beginner and self learner. I only posted my humble code because it seemed to me that the idea was to do it with every image submission (or else I misunderstood the question). Your approach is a bulk approach to read all data in the sheet.
My approach is to read only the last image entered and thus search only for that specific file. So the script takes about 2 sec where 0.5 sec from Utilities.sleep is just waiting for the image upload. the loop stops in the first match. But I will study your code for next approaches. Thank you very much.

LeventK

@Sergio_Sa
You are right, the example code snippet is for bulk operation, but that was what @Chriss was trying to do. However, for sure it’s more appropriate to set an onChange(e) installable trigger in the gSheet container and set an on change trigger associated with a similar code so that the shared URL will be instantly recorded to the gSheet everytime an image is saved