Last active
April 29, 2024 07:46
-
-
Save vperezb/6bdcdd23ea2754f152ffd7c9c82b127e to your computer and use it in GitHub Desktop.
Execute Google Sheets Macro or Apps Script Programatically using python and Google Apps Script API
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ''' | |
| Gist from the post https://medium.com/@victor.perez.berruezo/execute-google-apps-script-functions-or-sheets-macros-programmatically-using-python-apps-script-ec8343e29fcd | |
| Code partially from Google Apps Script Quickstart https://developers.google.com/apps-script/api/quickstart/python | |
| ''' | |
| import pickle | |
| import os.path | |
| from googleapiclient import errors | |
| from googleapiclient.discovery import build | |
| from google_auth_oauthlib.flow import InstalledAppFlow | |
| from google.auth.transport.requests import Request | |
| # If modifying these scopes, delete the file token.pickle. | |
| SCOPES = ['https://www.googleapis.com/auth/spreadsheets.currentonly'] | |
| def get_scripts_service(): | |
| """Calls the Apps Script API. | |
| """ | |
| creds = None | |
| # The file token.pickle stores the user's access and refresh tokens, and is | |
| # created automatically when the authorization flow completes for the first | |
| # time. | |
| if os.path.exists('token.pickle'): | |
| with open('token.pickle', 'rb') as token: | |
| creds = pickle.load(token) | |
| # If there are no (valid) credentials available, let the user log in. | |
| if not creds or not creds.valid: | |
| if creds and creds.expired and creds.refresh_token: | |
| creds.refresh(Request()) | |
| else: | |
| # Credentials path from the credentials .json file | |
| # from step 3 from Google Cloud Platform section | |
| flow = InstalledAppFlow.from_client_secrets_file( | |
| '.credentials/client_id.json', SCOPES) | |
| creds = flow.run_local_server(port=0) | |
| # Save the credentials for the next run | |
| with open('token.pickle', 'wb') as token: | |
| pickle.dump(creds, token) | |
| return build('script', 'v1', credentials=creds) | |
| service = get_scripts_service() | |
| # API ID from step 3 in Google Sheets/Script section | |
| API_ID = "YOUR_API_ID___NO_SCRIPT_ID" | |
| # Instead macro_test select your macro function name | |
| # from step 5 in Sheets/Script section | |
| request = {"function": "macro_test"} | |
| try: | |
| response = service.scripts().run(body=request, scriptId=API_ID).execute() | |
| except errors.HttpError as error: | |
| # The API encountered a problem. | |
| print(error.content) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment