以编程方式将Drive图像放入Sheets单元的正确方法是什么？ [英] What is the right way to put a Drive image into a Sheets cell programmatically?

问题描述

我有一个Web工作站将工作网站数据收集到Google表格电子表格中，并将工作网站照片收集到我为每项作业创建的Google云端硬盘文件夹中。某些（但不是全部）照片必须可以在Google表格的单元格中查看，以便表单可以作为工作完成报告的一部分进行打印。

我使用Google Picker将文件上传到特定于工作网站作业的文件夹。

我已成功设置单元格公式，例如 = IMAGE（hllp： //i.imgur.com/yuRheros.png，4，387，422），但只能从网络上的其他位置获取图片。

使用永久链接技巧像这样 = IMAGE（hllp：//drive.google.com/uc？export = view& id = 0B8xy-TdDgbjiFoOldUlLT091NXM，4，387， 422）不起作用;我认为它不会容忍谷歌在这些链接上进行的URL重定向。

我读过的其他方法，但还没有尝试过，是将实际的blob写入单元格。但是，我怀疑我会失去对后续格式化报告的控制权。

也许我需要用几种方法在几个单元格中记录图像规格：

1. 其Google Drive哈希键


2. 其维度
   
3. 它在imgur.com（或类似的）中的替代位置


4. 其blob

以编程方式获取Google重定向的图片最终到达网址，相当于打开图片并手动复制网址？人们可以永远信任它，还是会随着时间的推移而改变它？

更新：我错了= IMAGE（hllp：// drive.google.com/uc?export=view&id=0B8xy-TdDgbjiFoOldUlLT091NXM，4，387，422）无法正常工作。即使电子表格的所有者也是该图片的所有者，将图片分享给有链接的任何人是非常重要的。

我将去录音只是1.hash键和2.dimensions作为我的解决方案，但我很高兴知道是否有其他人有更好的主意。

解决方案

1. 转到工具。
   >脚本编辑器。


2. 复制并粘贴以下代码
   

3. 点击run for permission
   
   

    函数DRIVE_IMAGE（link）{
    prefix_url =https://docs.google.com/uc?export=download&; 
    link.replace（open？，uc？export = download&）; 
   } 
     






使用脚本：





1. 复制图片在Google云端硬盘中的分享链接。


2. li>
   

3. 输入公式
   
   

     =图片（DRIVE_IMAGE（COPIED_LINK））
     







I have a WebApp that collects work site data into a Google Sheets spreadsheet and also collects work site photos into a Google Drive folder that I create for each job. Some, but not all, of the photos must be viewable in a cell in Google Sheets, such that the sheet can be printed as part of a job completion report.

I use Google Picker to upload files to the folder specific to the work site job. I am unsure of the best way to use them from there.

I have had success setting a cell formula such as =IMAGE("hllp://i.imgur.com/yuRheros.png", 4, 387, 422), but only with images pulled from elsewhere on the web.

Using the permalink trick like this =IMAGE("hllp://drive.google.com/uc?export=view&id=0B8xy-TdDgbjiFoOldUlLT091NXM", 4, 387, 422) does not work; I think it won't tolerate the URL redirect that Google does on those links.

The other way I have read about, but not tried yet, is to write the actual blob into the cell. However, I suspect I will lose any control over subsequent formatting of the report.

Perhaps I am going to need to record the image specification in several ways in several cells:

1. its Google Drive hash key
2. its dimensions
3. its alternate location in imgur.com (or similar)
4. its blob

Is there a programmatic way to get Google's redirected final URL for an image, equivalent to opening the image and copying the URL by hand? Could one trust it for ever, or does it change over time?

Update : I am wrong about =IMAGE("hllp://drive.google.com/uc?export=view&id=0B8xy-TdDgbjiFoOldUlLT091NXM", 4, 387, 422) not working. It is essential that the image be shared to "anyone with the link", even if the owner of the spreadsheet is also the owner of the image.

I am going to go with recording just 1.hash key and 2.dimensions as my solution, but I'd be delighted to know if anyone else has a better idea.

解决方案

I made a two lines script to use the share link of an image in Google Drive.

1. Go to Tools > Script editor.
2. Copy, paste the following code

3. Click on run for permission

   function DRIVE_IMAGE(link){
       prefix_url = "https://docs.google.com/uc?export=download&";
       link.replace("open?", "uc?export=download&");
   }
   

Using the script :

1. Copy the share link of your image in Google Drive.
2. Go to a cell

3. Enter the formula

   =IMAGE(DRIVE_IMAGE("COPIED_LINK"))
   

这篇关于以编程方式将Drive图像放入Sheets单元的正确方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！