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

问题描述

我有一个 Web 应用程序，可将工作现场数据收集到 Google 表格电子表格中，并将工作现场照片收集到我为每个工作创建的 Google 云端硬盘文件夹中.某些(但不是全部)照片必须可在 Google 表格的单元格中查看，这样表格才能作为作业完成报告的一部分进行打印.

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.

我使用 Google Picker 将文件上传到特定于工作现场作业的文件夹.我不确定从那里使用它们的最佳方式.

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.

我已经成功设置了一个单元格公式，例如 =IMAGE("hllp://i.imgur.com/yuRheros.png", 4, 387, 422)，但仅限于图像从网络上的其他地方提取.

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.

使用永久链接技巧之类的这个 =IMAGE("hllp://drive.google.com/uc?export=view&id=0B8xy-TdDgbjiFoOldUlLT091NXM", 4, 387, 422) 不起作用；我认为它不会容忍 Google 在这些链接上进行的 URL 重定向.

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.

我读过但还没有尝试过的另一种方法是将实际的 blob 写入单元格中.但是，我怀疑我将无法控制报告的后续格式.

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. 它的 Google Drive 哈希键
2. 尺寸
3. 它在 imgur.com(或类似网站)中的备用位置
4. 它的斑点

是否有一种编程方式可以获取 Google 为图片重定向的最终网址，相当于打开图片并手动复制网址?可以永远相信它，还是会随着时间而改变?

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?

更新:我错了 =IMAGE("hllp://drive.google.com/uc?export=view&id=0B8xy-TdDgbjiFoOldUlLT091NXM", 4, 387, 422) 不是在职的.必须将图像分享给知道链接的任何人"，即使电子表格的所有者也是图像的所有者.

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.

我打算只记录 1.hash key 和 2.dimensions 作为我的解决方案，但我很高兴知道其他人是否有更好的主意.

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.

推荐答案

我制作了一个两行脚本来使用 Google Drive 中图像的共享链接.

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

1. 转到工具">脚本编辑器".
2. 复制、粘贴以下代码
3. 保存

function DRIVE_IMAGE(link){
  return link.replace("open?", "uc?export=download&");
}

使用脚本:

1. 复制您在 Google 云端硬盘中的图像的获取共享链接(也许您需要将共享首选项设置为在网络上公开).
2. 转到 Google 表格单元格.

3. 输入公式

1. Copy the Get shareable link of your image in Google Drive (Maybe you need to set the share preference to Public on the web).
2. Go to a Google sheets cell.

3. Enter the formula

=IMAGE(DRIVE_IMAGE("COPIED_LINK"))

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