0

TLDR; Can a .py script run a Google Apps Script?

Hi!

By using Google APIs I've managed to create a .py script that can access and edit a google sheet. I then want the program to be able to select a custom UI tab in the sheet and run the function (written in the inbuilt Google Apps Script).

If there's an easier way to do this, any input is greatly appreciated! :)

The program i wrote is here:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint
import datetime

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)

client = gspread.authorize(creds)

#^this authorizes the programs mail and stuff

sheet = client.open("Proggetest").sheet1 #opens sheet

now = datetime.datetime.now()
ctime = now.strftime("%d.%m.%Y %H:%M:%S") #time and date

sheet.update_cell(5, 5, "updated by prog. : " + ctime) 
#updates cell 5E


#INSERT CODE THAT RUNS APPS SCRIPT HERE:)

The UI piece in the sheet is here(written in the inbuilt Google Apps Script)

var ui = SpreadsheetApp.getUi();

function buildTESTmenu() {

  ui.createMenu('Tests')
    .addSubMenu(ui.createMenu('colours')
      .addItem('Change a cells colour', 'TESTmenu.changeColour')) #.changeColour pulls a function that changes the cells colour
    .addSubMenu(ui.createMenu('Data')
      .addItem('Change number in cell', 'TESTmenu.setValue'))#.setValuepulls a function that changes the cells value to a set number
    .addToUi()
}

Any help is greatly apreciated!

Thanks!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Ole Gunnar
  • 11
  • 1
  • Although I'm not sure whether I could correctly understand about your question, in the current stage, UI cannot be managed from outside. But in the case of the functions of Google Apps Script without using UI, these functions can be executed using Web Apps with the access token retrieved from the service account. How about this? If I misunderstood your question, I apologize. – Tanaike Jan 31 '21 at 23:51
  • You understood the question correctly:) I'm not that prelific in coding, so the UI thing was just an ide to how one could execute the GAS script:) But a quick followup question; whats "Web Apps"? Thanks for the feedback btw<3 – Ole Gunnar Feb 01 '21 at 12:31
  • Thank you for replying. About Web Apps, you can see the detail of it at [here](https://github.com/tanaikech/taking-advantage-of-Web-Apps-with-google-apps-script). When Web Apps is used, the functions of Google Apps Script can be run by requesting from python script. And, when you want to use the access token for requesting, you can also achieve it. Also, you can run the functions without using the access token. Although I'm not sure about the detail of `TESTmenu.changeColour` and `TESTmenu.setValue`, do you want to run these functions using python? Is my proposal the same with your direction? – Tanaike Feb 01 '21 at 12:40
  • @Tanaike `TESTmenu.changeColour` and `TESTmenu.setValue` are just some example functions i conjured up:) I'm volunteering as a budget coordinator of a student festival, where all the budgets are written on google sheets. To grant editing access to all of the budgets to a new person is a very tedious process, though we have a GA script on the sheets that does this automatically. The .py is supposed to open all the sheets and run the script. What do you think about "TheMaster"s answer? is the google-apps-script-api better suited for the task than creating a Web app? Thanks again:)) – Ole Gunnar Feb 01 '21 at 18:35
  • Thank you for replying. I also think that the functions of Google Apps Script can be run by Google Apps Script API. But the service account cannot use Apps Script API. This has already been mentioned in the existing answer. So as a workaround, I proposed to use Web Apps. Because I thought that the workaround can achieve your goal. From your replying, also I think that your goal can achieve with the Web Apps. – Tanaike Feb 02 '21 at 00:39
  • Ok, cool! Thanks<3 – Ole Gunnar Feb 07 '21 at 18:09

1 Answers1

2

You can run through from . But there are limitations. Most importantly, service accounts can't be used with the api. Next, I don't think you can manipulate the ui, when function is called in a execution api context.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Aaah I see! But if i cannot use the sevice account what can i use to make the program work? A different kind of account? Sorry if i'm a little slow, I've only had one semester of python programing, so i am learning this for fun on the side :)). Thanks for the quick response btw! I really appreciate it<3 – Ole Gunnar Jan 31 '21 at 15:31
  • @OleGunnar You can use a normal Google account/your personal gmail account. – TheMaster Jan 31 '21 at 16:09