Can i use an external program to run custom scripts in google sheets?

Question

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!

Answer 1

You can run google-apps-script through google-apps-script-api from python. 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.