Using the Execution API

The Apps Script Execution API consists of a single scripts resource, which has a single method, run, that makes calls to specific Apps Script functions. The run method must be given the following information when called:

In addition, the run method can also be provided:

  • A development mode boolean that, if true, causes the API to always execute the most recently saved version of the script, rather than the most recently published version. Only the owner of the script can execute it in development mode.

Requirements

In order for the run method to call an Apps Script function, the following must be true:

  • The Apps Script project containing the function must be deployed as an API executable. Projects can be deployed, undeployed and redeployed as needed. It is also possible to deploy a specific version of a project to, for example, release a stable version while continuing to develop the next version.

  • The calling application must supply the API a valid OAuth token. This OAuth token must cover all the scopes used by the script (not just the ones used by the called function).

  • The script and the calling application must share the same Cloud Platform project. You can use the default project created with the Apps Script project, create a new Cloud Platform project, or use an existing Cloud Platform project.

General procedure

Before using the API you need to do the following to meet the above requirements:

  1. Create an Apps Script project to call, with the functions you want to use. The API can also be used to call existing scripts that were created for other projects. Open the project in the Apps Script editor.

  2. Deploy the script project for execution by selecting Publish > Deploy as API executable. Choose a version (or create a new one) and who has access, then click Deploy. The new dialog that opens shows your script's ID, listed under "Current API ID". Make note of this ID — you need to enter it into the application code so that the API knows which script to call. If you need to find it again later, select Publish > Deploy as API executable in the code editor to see it.

  3. Choose a Cloud Platform project and ensure both the calling application and the target script share it. If you use the script's default Cloud Platform project, the calling application should use that project to set up its OAuth credentials. This requires you to have access to the default Cloud Platform project, which may not be the case if the script resides in Team Drive. If you are using a new or existing Cloud Platform Project, you need to switch the Apps Script project to use it if you have not done so already.

  4. Enable the Google Apps Script Execution API in the Cloud Platform project. You can find directions for doing this in the Execution API Java Quickstart.

  5. Create a valid Client ID and client secret for the application in the Cloud Platform project. This is covered in the Execution API Java Quickstart.

  6. In the application code, generate an OAuth access token for the API call. This is not a token the API itself uses, but rather one the script requires. The token must be built using the Client ID and the scopes from the script (in the editor, under File > Project properties > Scopes). This also requires prompting the user to authorize the script. The Google client libraries, while not strictly necessary, can greatly assist in handling OAuth for the application.

At this point the API can be used to call functions in your target Apps Script project. Each API call consists of the following steps:

  1. Build an API request using the script ID, function name, and any required parameters. Make the run call to the API, including an OAuth token in the header. This call can be made through the client library service (as the quickstarts do) or via a basic POST request.

  2. Allow the script to finish executing. Scripts are allowed to take up to six minutes of execution time, so your application should allow for this.

  3. Upon finishing, the script function may return a value, which the API delivers back to the application if the value is a supported type.

The full process for using the API is shown in the quickstarts. These quickstarts make use of the respective client libraries to simplify the enabling, authorization, and use of the API.

Parameter and return types

Using the Execution API usually involves sending data to Apps Script (as function parameters) and getting data back (as function return values). For strongly-typed languages (like Java), it is important to be aware of the types of these variables.

The basic types in Apps Script are similar to the basic types in JavaScript: strings, arrays, objects, numbers and booleans. The Execution API can only take and return values corresponding to these basic types -- more complex Apps Script objects (like a Document or Sheet) cannot be passed by the API.

When passing in parameters in a strongly-typed language, the parameters are provided as a list or array of generic objects corresponding to these basic types. In many cases simple type conversions can be applied automatically; for example, a function that takes a number parameter can be given a Java Double or Integer or Long object as a parameter without extra handling.

When the API returns the function response, it is often necessary to cast the returned value to the correct type before it can be used. Here are some Java-based examples:

  • Numbers returned by the API to a Java application arrive as java.math.BigDecimal objects, and may need to be converted to Doubles or int types as needed.
  • If the Apps Script function returns an array of strings, a Java application casts the response into a List<String> object:

    List<String> mylist = (List<String>)(op.getResponse().get("result"));
    
  • If you are looking to return an array of Bytes, you may find it convenient to encode the array as a base64 String within the Apps Script function and return that String instead:

    return Utilities.base64Encode(myByteArray); // returns a String.
    

Examples of interpreting the API response are shown in quickstarts and in the code samples below.

API request examples

The following shows how to make a simple request of the API, once a service is created and authorized:

Target Script

/** This is the Apps Script method these API examples will be calling.
 *
 *  It requires the following scope list, which must be used when authorizing
 *  the API:
 *    https://www.googleapis.com/auth/spreadsheets
 */

/**
 * Return a list of sheet names in the Spreadsheet with the given ID.
 * @param {String} a Spreadsheet ID.
 * @return {Array} A list of sheet names.
 */
function getSheetNames(sheetId) {
  var ss = SpreadsheetApp.openById(sheetId);
  var sheets = ss.getSheets();
  return sheets.map(function(sheet) {
    return sheet.getName();
  });
}

Java

// ID of the script to call. Acquire this from the Apps Script editor,
// under Publish > Deploy as API executable.
String scriptId = "<ENTER_YOUR_SCRIPT_ID_HERE>";

// Apps Script function to call.
String functionName = "getSheetNames";

// Initialize parameters for that function.
String sheetId = "<ENTER_ID_OF_SPREADSHEET_TO_EXAMINE_HERE>";
List<Object> params = new ArrayList<Object>();
params.add(sheetId);

// Create execution request.
ExecutionRequest request = new ExecutionRequest()
        .setFunction(functionName)
        .setParameters(params)
        .setDevMode(true);  // Optional.

try {
    // Make the request.
    Operation op =
            service.scripts().run(scriptId, request).execute();

    // Print results of request.
    if (op.getError() != null) {
        // The API executed, but the script returned an error.
        Map<String, Object> detail = op.getError().getDetails().get(0);
        System.out.println(
                "Script error! Message: " + detail.get("errorMessage"));
    } else {
        // Here, the function returns an array of strings, so the
        // result must be cast into a Java List<String>.
        List<String> sheetNames =
                (List<String>)(op.getResponse().get("result"));

        System.out.println("Sheet names in spreadsheet:");
        for (String name: sheetNames) {
            System.out.printf("\t%s\n", name);
        }
    }
} catch (GoogleJsonResponseException e) {
    // The API encountered a problem before the script was called.
    e.printStackTrace(System.out);
}

JavaScript

// ID of the script to call. Acquire this from the Apps Script editor,
// under Publish > Deploy as API executable.
var scriptId = "<ENTER_YOUR_SCRIPT_ID_HERE>";

// Initialize parameters for function call.
var sheetId = "<ENTER_ID_OF_SPREADSHEET_TO_EXAMINE_HERE>";

// Create execution request.
var request = {
    'function': 'getSheetNames',
    'parameters': [sheetId],
    'devMode': true   // Optional.
};

// Make the request.
var op = gapi.client.request({
    'root': 'https://script.googleapis.com',
    'path': 'v1/scripts/' + scriptId + ':run',
    'method': 'POST',
    'body': request
});

// Log the results of the request.
op.execute(function(resp) {
  if (resp.error && resp.error.status) {
    // The API encountered a problem before the script started executing.
    console.log('Error calling API: ' + JSON.stringify(resp, null, 2));
  } else if (resp.error) {
    // The API executed, but the script returned an error.
    var error = resp.error.details[0];
    console.log('Script error! Message: ' + error.errorMessage);
  } else {
    // Here, the function returns an array of strings.
    var sheetNames = resp.response.result;
    console.log('Sheet names in spreadsheet:');
    sheetNames.forEach(function(name){
      console.log(name);
    });
  }
});

Python

# ID of the script to call. Acquire this from the Apps Script editor,
# under Publish > Deploy as API executable.
SCRIPT_ID = '<ENTER_YOUR_SCRIPT_ID_HERE>'

# Initialize parameters for function call.
sheetId = '<ENTER_ID_OF_SPREADSHEET_TO_EXAMINE_HERE>'

# Create execution request.
request = {
    "function": "getSheetNames",
    "parameters": [sheetId],
    "devMode": True       # Optional.
    }

try:
    # Make the request.
    response = service.scripts().run(body=request,
            scriptId=SCRIPT_ID).execute()

    # Print results of the request.
    if 'error' in response:
        # The API executed, but the script returned an error.
        error = response['error']['details'][0]
        print("Script error! Message: {0}".format(error['errorMessage']))
    else:
        # Here, the function returns an array of strings.
        sheetNames = response['response'].get('result')
        print('Sheet names in spreadsheet:')
        for name in sheetNames:
            print("\t{0}".format(name))

except errors.HttpError as e:
    # The API encountered a problem before the script started executing.
    print(e.content)

Ruby

# ID of the script to call. Acquire this from the Apps Script editor,
# under Publish > Deploy as API executable.
SCRIPT_ID = '<ENTER_YOUR_SCRIPT_ID_HERE>'

# Initialize parameters for function call.
sheet_id = '<ENTER_ID_OF_SPREADSHEET_TO_EXAMINE_HERE>'

# Create execution request.
request = Google::Apis::ScriptV1::ExecutionRequest.new(
  function: 'getSheetNames',
  parameters: [sheet_id],
  devMode: true   # Optional.
)

begin
  # Make the request.
  resp = client.run_script(SCRIPT_ID, request)

  # Print results of the request.
  if resp.error
    # The API executed, but the script returned an error.
    error = resp.error.details[0]
    puts "Script error! Message: #{error['errorMessage']}"
  else
    # Here, the function returns an array of strings.
    sheet_names = resp.response['result']
    puts "Sheet names in spreadsheet:"
    sheet_names.each do |name|
      puts "\t#{name}"
    end
  end
rescue Google::Apis::ClientError
  # The API encountered a problem before the script started executing.
  puts "Error calling API!"
end

Limitations

The Apps Script Execution API has several limitations:

  1. The script being called and the calling application must share a Cloud Platform project. This can be the default one created with the script, or another project (which requires switching the script to use that project). If using the default project of a script, be aware that when a script resides in a Team Drive your access to its Cloud Platform project may be restricted.
  2. The API cannot return Apps Script-specific objects (such as Documents, Blobs, Calendars, Folders, etc.) to the application -- only basic types such as strings, arrays, objects, numbers and booleans.
  3. The API can only execute scripts that have at least one required scope. This means you cannot use the API to call a script that does not require authorization of one or more services.
  4. The API cannot create Apps Script triggers.

Send feedback about...

Apps Script
Apps Script