There's a way to import photos on Google sheet, in such a way that i have the link to the photo (with. jpg)?

Support
1

Before the v1.14 i was able to import the link of a photo directly in Google sheet, now it imports the link but shows only the name of the image in the tab. So if i upload the Google Sheet in Google Maps it can't recognize the link to the photo. How can I solve this problem? The optimal solution would be to Save the link directly to a .jpg on the internet, so that Google Maps can recognize the link as a photo.

2

There are two pieces of software with v1.14 that talk to Google Sheets. Are referring to Collect or Aggregate?

3

I am referring to Odk collect

4

Hi @Nicola_Pace

sorry for any inconveniences. I would recommend using Google Sheet Script in such a case. Here is a script I prepared based on https://productforums.google.com/forum/#!topic/docs/ymxKs_QVEbs

function extractHyperlinks() {
  var prefix = '\\"';
  var suffix = '\\"';
  var prefixToSearchFor = new RegExp(prefix, "i");
  var suffixToSearchFor = new RegExp(suffix, "i");
  var prefixLength = 1;
  
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
         
  for each (var sheet in SpreadsheetApp.getActiveSpreadsheet().getSheets()) {
    var selection = sheet.getDataRange();
    var columns = selection.getNumColumns();
    var rows = selection.getNumRows();
    for (var column = 1; column <= columns; column++) {
      for (var row = 1; row <= rows; row++) { 
        var cell = selection.getCell(row,column);
        cellFormula = cell.getFormula();
        
        // only proceed if the cell contains a formula
        // if the leftmost character is "=", it contains a formula
        // otherwise, the cell contains a constant and is ignored
        // does not work correctly with cells that start with '=
        if (cellFormula[0] == "=") {
          // find the prefix
          prefixFoundAt = cellFormula.search(prefixToSearchFor);
          if (prefixFoundAt >= 0) { // yes, this cell contains the prefix
            // remove everything up to and including the prefix
            extractedTextString = cellFormula.slice(prefixFoundAt + prefixLength);
            // find the suffix
            suffixFoundAt = extractedTextString.search(suffixToSearchFor);
            if (suffixFoundAt >= 0) { // yes, this cell contains the suffix
              // remove all text from and including the suffix
              extractedTextString = extractedTextString.slice(0, suffixFoundAt).trim();
              
              // store the plain hyperlink string in the cell, replacing the formula
              cell.setValue(extractedTextString);
            }
          }
        }
      }
    }
  }     
}

To use this script you need to select Tools -> Script Editor` in your Google Spreadsheet. Then you need to paste my script and run it. It should extract all hyperlinks.

I thin it's not a common case and using hyperlinks is ok, what about recommending such a script I prepared? Maybe we can add the script to the documentation.

1 Like
5

Thanks a lot, now it works fine.

6

Keep in mind you need to run this script each time you receive new data with hyperlinks, or each time you want to use such a spreadheet in a way you described above.