按下按钮运行从Google工作表保存在本地计算机中的python脚本 [英] Running a python script saved in local machine from google sheets on a button press

问题描述

我正在尝试使用在Google工作表中的一行中填充的数据来创建Jira问题，我计划放置一个按钮以读取行的内容并创建Jira问题，我发现Jira API为此编写了脚本，并且还有Google表格API，用于读取要放入Jira API中的行值.

我如何以一种简单的方式将按钮链接到本地​​计算机中的python脚本，我在这里遇到了其他类似的问题，但是它们已经很老了，希望现在可以使用一些新的方法.

请帮助我以一种简单的方式实现这一目标，我们将不胜感激.

谢谢您并保持安全.

解决方案

不幸的是，您真的无法在Google表格中按下按钮来启动本地Python脚本-Google表格/您的浏览器无法访问您的本地文件和程序.

您可以创建一个按钮，该按钮运行 Google Apps脚本(GAS).这是一些基于JavaScript的代码，已附加到电子表格中，由Google托管/运行.这里是有关如何通过按钮按下来运行的教程.>

如果您可以将脚本移植到GAS中，那就是一种解决方案.

如果要将脚本保留在Python中，则基本上需要进行部署，然后使用GAS调用Python脚本.我能想到的最简单的方法(这不是超级简单，但完全可行！):

1.使您的Python脚本成为API.

使用类似烧瓶或 导入FastAPI应用= FastAPI()def main():打印(通过API访问Google表格...")#您的代码在这里打印(通过API上传到JIRA ...")#您的代码在这里@ app.get("/")def root():主要的()返回{"message":"Done"}

此处，"/"表示是API端点.当您访问(或发出"get"请求)部署的应用程序的URL时，只需以"/"结尾，就会调用 root 函数，该函数会调用您的主要功能.(您可以设置不同的URL结尾来做不同的事情.)

我们可以在本地进行测试.如果您遵循FastAPI的设置说明，则应该能够运行命令 uvicorn main:app --reload ，该命令将启动位于 http://127.0.0.1:8000 .如果您在浏览器中访问该URL，则脚本将开始运行，并显示消息完成".应该会出现在您的浏览器中.

2.部署您的Python应用

有许多可以托管您的Python程序的服务，例如 Heroku 或 Google云.他们可以提供免费试用，但这通常要花钱.FastAPI包含有关部署到Deta 的说明，目前似乎有免费层.

当您的应用正在运行时，应该有一个关联的网址，例如"https://1kip8d.deta.dev/".如果您在浏览器中访问它，它将运行您的脚本并返回"Done".消息.

3.使用GAS从Google Sheets中命中您的Python API

最后一步是命中"使用GAS而不是在浏览器中手动访问该URL.遵循上述教程，创建一个链接到您的GAS脚本电子表格，以及分配"的按钮.到您的脚本.该脚本将如下所示:

  function myFunction(){var response = UrlFetchApp.fetch("https://1kip8d.deta.dev/");Logger.log(response.getContentText());} 

现在，只要您按一下按钮，GAS就会访问该URL，这将导致您的Python脚本执行.

I am trying to create Jira issues with data populated in a row in google sheet, I plan to put a button to read the contents of the row and create Jira issues, I have figured the Jira API wrote the script for it and also the Google sheets API to read the row values to put in the Jira API.

How do I link the button to the python script in my local machine in a simple manner, I went through other similar asks here, but they are quite old and hoping now some new way might be available.

Please help me achieve this in a simple way, any help is greatly appreciated.

Thank You and Stay Safe.

解决方案

Unfortunately, you really can't get a button press in a Google Sheet to launch a local Python script-- Google Sheets / your browser cannot access your local files and programs in that way.

You can create a button that runs a Google Apps Script (GAS). This is some code based on JavaScript, attached to the spreadsheet, hosted/run by Google. Here's a tutorial on how to run via button press.

If you can port your script into GAS, that is one solution.

If you want to keep the script in Python, you basically need to deploy it and then use GAS to call your Python script. The simplest way I can think of (which is not super simple, but is totally doable!) is as follows:

1. Make your Python script into an API.

Use something like Flask or FastAPI to setup your own API. The aim that when a certain URL is visited, it will trigger your Python program to run a function which does all the work. With FastAPI it might look like this:

from fastapi import FastAPI

app = FastAPI()


def main():
    print("Access Google Sheet via API...")
    # your code here

    print("Upload to JIRA via API...")
    # your code here


@app.get("/")
def root():
    main()
    return {"message": "Done"}

Here, "/" is the API endpoint. When you visit (or make a "get" request) to the URL of the deployed app, simply ending in "/", the root function will get called, which calls your main function. (You could set up different URL endings to do different things).

We can test this locally. If you follow the setup instructions for FastAPI, you should be able to run the command uvicorn main:app --reload which launches a server at http://127.0.0.1:8000. If you visit that URL in your browser, the script should get run and the message "Done" should appear in your browser.

2. Deploy your Python app

There are many services that can host your Python program, such as Heroku or Google Cloud. They may offer free trials but this generally costs money. FastAPI has instructions for deploying to Deta which seems to currently have a free tier.

When your app is app and running, there should be an associated web address such as "https://1kip8d.deta.dev/". If you access this in the browser it will run your script and return the "Done" message.

3. Hit your Python API from Google Sheets, using GAS

The last step it to "hit" that URL using GAS, instead of visiting it manually in the browser. Following the tutorial mentioned above, create a GAS script linked to your spreadsheet, and a button which is "assigned" to your script. The script will look something like this:

function myFunction() {
  var response = UrlFetchApp.fetch("https://1kip8d.deta.dev/");
  Logger.log(response.getContentText());
}

Now, whenever you press the button, GAS will visit that URL, which will cause your Python script to execute.

这篇关于按下按钮运行从Google工作表保存在本地计算机中的python脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！