获取Google Spreadsheet单元格的图像URL [英] Getting a Google Spreadsheet Cell's Image URL

问题描述

我有一个Google Spreadsheet，在每行的第二列和第三列上都有一个图像，并且我试图将所有电子表格数据记录到控制台(目前).我在访问每个单元格中的图像时遇到问题，因为getValue()函数不会返回图像的源URL.使用=image("http://imagesource.com",3)将所有图像都插入到电子表格中.到目前为止，这是我从Google提供的默认readRows函数中编辑的内容:

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);
        }
      }
    }
  }

编辑:看起来我可以使用getFormula()返回看起来像=image("http://imagesource.com",3)的字符串，但是仍然存在处理字符串以仅包含URL的问题.我不确定这是否可行，因为字符串看起来像是Google脚本中的原始数据类型.

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.

推荐答案

Range.getValue()返回单元格的计算值，在这种情况下，该单元格是图像而不是URL.如您所见，您可以使用getFormula()来获取创建图像的公式.您可以使用正则表达式从公式字符串中提取图像URL.

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;
}

如果该URL是从另一个单元格或公式中计算得出的，则此方法将无效，但它应适用于您列出的简单情况.

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.

这篇关于获取Google Spreadsheet单元格的图像URL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！