是否可以在Google电子表格中创建宏以执行python代码? [英] Is possible to make a macro in google spreadsheet to execute a python code?

问题描述

我个人觉得用python编写脚本更自在，而且比宏有更多的库可供使用.但是如果没有选择的话..预先谢谢你.

Personally i feel more confortable making a script in python and there are more libraries to use than a macro. But if there is no option.. Thank you in advance.

推荐答案

您可以通过使用 onOpen(e)触发器并托管您的 python脚本来解决您的问题在Google Cloud上.

Your issue can be solved by using an onOpen(e) trigger and by hosting your python script on Google Cloud.

1.使用 onOpen(e)触发器

1. Use the onOpen(e) trigger

每次打开电子表格时， onOpen(e)触发器用于在电子表格中创建菜单 MY MENU .此外，子菜单项 TRIGGER PYTHON SCRIPT 将具有与功能 runScript()关联的功能，并且每次单击该功能时，该功能都将运行.

The onOpen(e) trigger is used to create the menu MY MENU in your Spreadsheet each time the Spreadsheet opens. Moreover, the submenu item TRIGGER THE PYTHON SCRIPT will have the function runScript() associated with it and each time you click it, that function will be run.

可以通过在Apps脚本中使用此代码段来实现上述行为

The above behavior can be achieved by using this snippet in Apps Script

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('MY MENU')
      .addItem('TRIGGER THE PYTHON SCRIPT', 'runScript')
      .addToUi();
}

2.在Google Cloud上托管您的Python脚本

您应该将Python脚本作为 Cloud Function 托管在Google Cloud中，然后运行代码.

You should host your Python Script in Google Cloud as a Cloud Function and run the code.

runScript()是由 TRIGGER PYTHON SCRIPT 菜单从上方触发的函数，该代码段类似于以下内容.

The runScript() is the function triggered by TRIGGER THE PYTHON SCRIPT menu from above and the snippet looks something like this.

function runScript() {
  var params = {
    'method': 'post',
    'headers': {
      'contentType': 'application/json',
      'payload': '{"name":"Name"}'
    }
  };
  var pyScript = UrlFetchApp.fetch('https://YOUR_REGION-YOUR_PROJECT_ID.cloudfunctions.net/FUNCTION_NAME', params);
}

注意:您可能必须根据脚本和所需的操作来更改 params .

Note: You might have to change the params based on your script and your desired actions.

参考

• 应用脚本触发器;

应用脚本UrlFetchApp ;

Google云功能.

这篇关于是否可以在Google电子表格中创建宏以执行python代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！