您可以编写可绘制内容的Google表格功能吗? [英] Can you write a Google Sheets function that draws something?

问题描述

是否可以在Google表格脚本中编写自己的自定义函数，以返回绘制的图像，类似于SPARKLINE函数的工作方式，只不过我想制作一个绘制饼图的函数.

我不不想使用插入>图表...>饼图，因为这会在电子表格的顶部创建一个浮动图表.我希望能够编写自己的函数，该函数将返回一个饼形图，该饼形图嵌入在该函数所输入的单元格中，就像您可以使用迷你图来处理柱形图，条形图和折线图一样.

解决方案

以下想法如何?此示例脚本使用电子表格上的自定义功能将图表嵌入到单元格中.我认为这种方法是各种各样的想法之一.

问题:

当您要创建图表并使用自定义函数将其嵌入到单元格时，您会注意到不能使用 insertChart().使用自定义功能有一些限制.但是 insertChart()创建浮动图表.因此，为了将图表嵌入到单元格中，函数 = IMAGE()适用于这种情况.在这里，用于设置 = IMAGE()的 setFormula()和用于从图表创建图像的 DriveApp.createFile()不能用于自定义功能

解决方案:

为了避免这些限制，我使用了

注意:

1. 使用自定义函数 embedChart()时，加载时间约为40秒.(我不知道这是否仅在我的环境中发生.)
2. 创建的图像的权限为 ANYONE_WITH_LINK ，查看.
3. embedChart()被 = IMAGE()覆盖.因此，重新打开电子表格时， = IMAGE()的响应比 embedChart()的响应快得多.

如果我误解了你的问题，对不起.

Is it possible to write your own custom function in google sheets script that returns a drawn image, similar to how the SPARKLINE function works, except I want to make one that draws a pie chart instead.

I do not want to use Insert > Chart... > Pie Chart because that creates a floating chart on top of the spreadsheet. I would like to be able to write my own function that would return a pie chart that is embedded within the cell that the function is entered in, just like you can do with columns, bars, and line charts using sparkline.

解决方案

How about following idea? This sample script embeds a chart to a cell using custom function on Spreadsheet. I think that this method is one of various ideas.

Problems :

When you want to create a chart and embed it to a cell using custom functions, you notice that insertChart() cannot be used. There are some limitations for using custom functions. But insertChart() creates floating charts. So in order to embed a chart to a cell, the function =IMAGE() is suitable for this situation. Here, setFormula() for setting =IMAGE() and DriveApp.createFile() for creating images from charts also cannot be used for custom functions.

Solution :

In order to avoid these limitations, I used Web Apps.

To use this sample script, please deploy Web Apps as follows.

On the Script Editor,

• File
  • -> Manage Versions
  • -> Save New Version
• Publish
  • -> Deploy as Web App
  • -> At Execute the app as, select "your account"
  • -> At Who has access to the app, select "Anyone, even anonymous"
  • -> Click "Deploy"
  • -> Copy "Current web app URL"
  • -> Click "OK"

When it deploys Web Apps, the approval required authorization can be done, simultaneously.

Sample Script :

Please copy and paste this script to a bound script of spreadsheet.

var folderId = "### Folder ID ###"; // This is a folder to save images.
var webappsurl = "https://script.google.com/macros/s/######/exec"; // Here, please put "Current web app URL".

function embedChart(range) {
  var ac = SpreadsheetApp.getActiveSheet().getActiveCell();
  var q1 = "?datarange=" + range;
  var q2 = "&row=" + ac.getRow();
  var q3 = "&col=" + ac.getColumn();
  var url = webappsurl + q1 + q2 + q3;
  UrlFetchApp.fetch(url);
}

function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(sheet.getRange(e.parameters.datarange))
    .setOption('height', 280)
    .setOption('width', 480)
    .setOption('title', 'Sample chart')
    .build();
  var file = DriveApp.getFolderById(folderId).createFile(
    chart.getAs('image/png').setName("chart_image.png")
  );
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  sheet.getRange(e.parameters.row, e.parameters.col).setFormula(
    '=IMAGE("' + "http://drive.google.com/uc?id=" + file.getId() + '")'
  );
}

Flow of Script :

embedChart()

1. Input =embedChart("a2:a6") in cell B7.
2. Using fetch(), sends data of a2:a6 and the inputted coordinate to doGet().

doGet()

1. Using doGet(), get the data.
2. Creates a chart using inputted range a2:a6. (in this case, creates a pie chart)
3. Saves a chart as an image. (in this case, saves as PNG)
4. Updates a permission of the image file to use for =IMAGE().
5. Embeds the image using =IMAGE() which was imported by setFormula().

Result :

By inputting =embedChart("a2:a6") in cell B7 as a custom function, following result can be obtained.

Note :

1. When the custom function embedChart() is used, loading time is about 40 seconds. (I don't know whether this occurs at only my environment.)
2. Permissions of the created image are ANYONE_WITH_LINK, VIEW.
3. embedChart() is overwritten by =IMAGE(). So when the spreadsheet is reopened, the response of =IMAGE() is much faster than that of embedChart().

If I misunderstand your question, I'm sorry.

这篇关于您可以编写可绘制内容的Google表格功能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！