3

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.

Improve this question
2
  • Can you call your Jira APIs from the internet? If so, Apps Script offers an UrlFetch service that can be used to call Jira APIs from the sheet. If not, you could build a button on your local machine to pull the spreadsheet data (via Drive or Sheets API) and push to Jira. But you'll need to trigger it from your machine; Google won't have access to remotely execute code on your computer. Commented Mar 21, 2021 at 3:41
  • To clarify, you have a Python script that reads your Google Sheet via Google Sheets API and then creates Jira Tasks via Jira API? Commented Mar 27, 2021 at 12:19

3 Answers 3

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3
+25

Google sheets cannot run code on your local machine. That means you have a few options:

Click the button locally

Instead of clicking a button on the google sheet, you can run the script yourself from the command line. This is probably how you tested it, and not what you want.

Watch the spreadsheet

You could have your python script setup to run every few minutes. This has the benefit of being very straightforward to setup (google for cron jobs), but does not have a button, and may be slower to update. Also, it stops working if you turn off your computer.

Make script available for remote execution

You can make it so that your script can be run remotely, but it requries extra work. You could buy a website domain, and point it towards your computer (using dynamic dns), and then make the google sheet request your new url. This is a lot of work, and costs real money. This is probably not the best way

Move the script into the cloud

This is probably what you want: cut your machine out of the loop. You can use Google AppScripts, and rewrite your jira code there. You can then configure the google AppScript to run on a button click.

Improve this answer

Comments

0

You might want to check out Google Colaboratory. It's a service by Google that can host your Python code (called a "notebook"), connect with your Google Drive (and other Google services), and make calls out to web endpoints (which would be your Jenkins server). I think those are the three pieces you're dealing with here.

Just to be clear... your code wouldn't be local anymore (if that's really important to you). Instead, it would be hosted by Google. The notebooks are saved to your Google Drive account, so you get the security that provides.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.