以编程方式将云端硬盘图像放入表格单元格的正确方法是什么? [英] 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) 不起作用；我认为它不会容忍谷歌在这些链接上所做的 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 的图片重定向最终 URL，相当于打开图片并手动复制 URL?可以永远相信它，还是会随着时间而改变?

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 键和 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"))

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