CODEWITHSUNDEEP
imagegoogle-sheetsgoogle-apps-scripturlgoogle-sheets-formula
Shwheelz
Shwheelz

Reputation: 553

Getting a Google Spreadsheet Cell's Image URL

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.

Upvotes: 4

Views: 8507

Answers (1)

Eric Koleda
Eric Koleda

Reputation: 12673

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.

Upvotes: 3

Related Questions