使用代码将Google Apps脚本功能分配给Google表格图像 [英] Assign a Google Apps Script function to a Google Sheets image with code

问题描述

使用代码将GOOGLE APPS脚本功能分配给图像.

ASSIGN a GOOGLE APPS SCRIPT FUNCTION TO an IMAGE WITH CODE.

通过单击Google表格中的图片，然后选择分配脚本"选项，就可以实现.

By clicking on a image in a Google Sheet and selecting the "Assign Script" option, it is possible.

但这是一项手动任务，我想使它自动化.

But this is a manual task and I want to automate this.

推荐答案

可以将图像插入Google表格并使用Apps脚本代码分配功能.

An image can be inserted into a Google Sheet and assigned a function with Apps Script code.

Sheet类有一个 insertImage()方法和一个可以在图像上运行的 assignScript()方法.

There is an insertImage() method for the Sheet Class and an assignScript() method that can be run on an image.

insertImage()方法需要图像mime类型的Blob.要获取jpeg MIME类型的图像，您可以绘制图像并保存到本地驱动器中，然后将其上传到Google云端硬盘中，然后使用代码将其作为Blob获取.

The insertImage() method requires a blob of an image mime type. To get an image of a jpeg mime type, you can draw and image, save it to a local drive, upload the image to your Google Drive, and then with code, get the image as a blob.

• 在Google表格中，单击插入"，然后单击绘图"
• 绘制图像-例如，看起来像按钮的图像
• 将图像保存到计算机驱动器中
• 在Google云端硬盘中，点击新建"和文件上传"，然后将图片文件上传到您的Google云端硬盘并为其命名
• 按照下面的测试功能所示，通过代码获取图像文件
• 使用代码将图像文件转换为blob
• 运行代码以插入图像并分配功能

代码示例:

请注意，只有刷新了带有电子表格的浏览器标签后，图片才会显示.

Note that the image doesn't show up until after the browser tab with the spreadsheet have been refreshed.

function setImage(po) {
try{
  var image,sh,ss;

  /*
    po.blobSource - a blob that is an image file type
    po.column - the column to set the image in
    po.functionName - the name of the function to assign to the image
    po.row - the row to set the image in
    po.shName - the name of the sheet tab
  */

  ss = SpreadsheetApp.getActiveSpreadsheet();//This code is bound to a Sheet
  sh = ss.getSheetByName(po.shName);
  image = sh.insertImage(po.blobSource, po.column, po.row);//Insert an image and return the image

  image.assignScript(po.functionName);//Assign an Apps Script function to the image

  return true;
}catch(e){
  //Logger.log('Error: ' + e.message)
  //Logger.log('stack: ' + e.stack)
  return false;
}
}

function testRun() {
  var blobSrc,contentType,file,files;

  /*
    In a Sheet click Insert and Drawing
    Draw a button
    Save the button to your computer drive
    From Google Drive click "New" and File Upload and upload the file and give it a name
    Get the image file in code as shown in this function
    Convert the image file to a blob
  */

  files = DriveApp.searchFiles('mimeType contains "jpeg" and title contains "Name of File Here"');
  if (files.hasNext()) {
   file = files.next();
  }

  contentType = 'image/jpeg';

  if (file) {
    blobSrc = file.getAs(contentType);
  }

  //Logger.log(blobSrc.getContentType());  

  //Logger.log(blobSrc.getName())
  setImage({shName:'Sheet1',blobSource:blobSrc,row:1,column:1,functionName:'myFunction'});

}

这篇关于使用代码将Google Apps脚本功能分配给Google表格图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！