Getting a Google Spreadsheet Cell's Image URL

Question

I have a Google Spreadsheet that has an image on the 2nd and 3rd column of each row, and I am trying to log all of the spreadsheet data to the console (for now). I'm having trouble accessing the image within each cell because the getValue() function will not return the source url for the image. All of the images were inserted into the spreadsheet using =image("http://imagesource.com",3). Here is what I have so far, edited from the default readRows function Google provides:

  function readRows() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var numCols = rows.getNumColumns();

    for (var i = 2; i <= numRows - 1; i++) {
      for (var j = 1; j <= numCols; j++) {
        // columns 2 and 3 always contain images  
        if (j == 2 || j == 3) {
          // gets the google spreadsheet url, not the original source url
          // of the image contained in the cell like I want it to
          var imgUrl = rows.getCell(i, j).getDataSourceUrl(); 
          Logger.log("[img]" + imgUrl + "[/img]");
        } else {
          var cellData = rows.getCell(i, j).getValue();
          Logger.log(cellData);
        }
      }
    }
  }

EDIT: Looks like I can use getFormula() to return a String that looks like =image("http://imagesource.com",3) but that still leaves me with the issue of manipulating the string to include just the URL. I'm not sure that's possible because strings look like they are a primitive data type in google scripts.

Answer 1

Range.getValue() returns the computed value of a cell, which in this case is an image, not a URL. As you've found, you can use getFormula() to get the formula that created the image. You can use a regular expression to extract the image URL from the formula string.

function getImageUrl(formula) {
  var regex = /=image\("(.*)"/i;
  var matches = formula.match(regex);
  return matches ? matches[1] : null;
}

This won't work if the URL is computed from another cell or formula, but it should work for the simple case you have listed.