如何获得“单元格中的图像"?来自Google Sheets API的网址 [英] How to get an "Image in cell" URL from the Google Sheets API

问题描述

我使用sheets.spreadsheets.values.get在电子表格中获取信息，但是包含图像的单元格显示一个空字符串值.

I use sheets.spreadsheets.values.get to get information inside a Spreadsheet, but the cells that contain images show an empty string value.

我尝试使用values:userEnteredValue作为请求的参数(如此问题

I tried using values:userEnteredValue as a parameter for the request (as shown in this question Retrieve images in cells using Google Sheets API), but that didn't work.

sheets.spreadsheets.values.get({
      spreadsheetId: 'XXX',
      range: 'firstSheet!A1:E10',
      key: "XXXX"
      //values: 'userEnteredValue'
}

这是我目前所拥有的

This is what I currently get and have

编辑: 我意识到我的问题之一是我应该使用spreadsheets.get时使用了spreadsheets.values.get.我已经更改了此设置，但仍然无法获取网址

EDIT: I realized one of my problems was that I used spreadsheets.values.get when I should be using spreadsheets.get. I have changed this, but I still cant manage to get the url

包含文本的单元格具有userEnteredValue属性，但是包含图像的单元格没有.

The cell that contains text has a userEnteredValue property, but the one that contains the image does not.

推荐答案

• 您要从单元格中的=image("https://www.google.com/images/srpr/logo3w.png")检索URL.
• 您要使用sheets.spreadsheets.values.get()的方法.
• 您想使用Node.js的googleapis实现此目的.
  • 您已经可以使用Sheets API.
  • You want to retrieve URL from =image("https://www.google.com/images/srpr/logo3w.png") in a cell.
  • You want to use the method of sheets.spreadsheets.values.get().
  • You want to achieve this using googleapis of Node.js.
    • You have already been able to use Sheets API.

    如果我的理解是正确的，那么该修改如何?

    If my understanding is correct, how about this modification?

    • =image("https://www.google.com/images/srpr/logo3w.png")作为公式放在单元格中.因此，为了检索公式，请使用valueRenderOption: "FORMULA".
    • =image("https://www.google.com/images/srpr/logo3w.png") is put to a cell as a formula. So in order to retrieve the formula, please use valueRenderOption: "FORMULA".

    此修改后的脚本假设将=image("https://www.google.com/images/srpr/logo3w.png")放在工作表名称为"Sheet1"的单元格"A1"中.

    This modified script supposes that =image("https://www.google.com/images/srpr/logo3w.png") is put in a cell "A1" of a sheet name of "Sheet1".

    sheets.spreadsheets.values.get(
      {
        spreadsheetId: "###",
        range: "Sheet1!A1",
        valueRenderOption: "FORMULA"
      },
      function(err, res) {
        if (err) {
          console.log(err);
          return;
        }
        const url = res.data.values[0][0].match(/https?:\/\/[\w\S][^"]+/)[0];
        console.log(url);
      }
    );
    

    结果:

    https://www.google.com/images/srpr/logo3w.png
    

    参考:

    • spreadsheets.values.get

    Reference:

    • spreadsheets.values.get

    如果我误解了你的问题，而那不是你想要的结果，我深表歉意.

    If I misunderstood your question and that was not the result you want, I apologize.

    • 您要通过电子表格中的INSERT选项"检索插入到单元格和工作表上的图像的URL.

    不幸的是，如果我的理解是正确的，那么在当前阶段，当通过电子表格中的INSERT选项"将图片从URL插入到单元格中时，Sheets API和Spreadsheet Service无法检索图片的URL.同样，当通过电子表格中的INSERT选项"从URL将图像插入到工作表中时，Sheets API和电子表格服务也无法检索到该图像的URL.对于这种情况，我表示歉意.

    If my understanding is correct, unfortunately, in the current stage, when an image is inserted in a cell from an URL by "the INSERT option in spreadsheet", the URL of image cannot be retrieved by Sheets API and Spreadsheet Service. Also when an image is inserted on a sheet from an URL by "the INSERT option in spreadsheet", the URL of image cannot be retrieved by Sheets API and Spreadsheet Service. I apologize for this situation.

    • 电子表格服务

    这篇关于如何获得“单元格中的图像"?来自Google Sheets API的网址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！