Service Account

Executing Google Apps Script with Service Account

Gists

Abstract

One day, you might have a situation where it is required to run Google Apps Script using the service account. Unfortunately, in the current stage, Google Apps Script cannot be directly run with the service account because of the current specification. So, this report introduces a workaround for executing Google Apps Script using the service account.

Introduction

When you want to execute Google Apps Script from outside of Google, as the basic approach, it can be achieved by Google Apps Script API. Ref In order to use Google Apps Script, it is required to link the Google Apps Script project with the Google Cloud Platform project. Ref But, in the current stage, Google Apps Script can be executed by Google Apps Script API with only the access token obtained from OAuth2. Unfortunately, the access token obtained by the service account cannot used for executing Google Apps Script using Google Apps Script API. It seems that this is the current specification on the Google side. However, there might be a case that it is required to execute Google Apps Script using the service account. In this report, I would like to introduce a workaround for executing Google Apps Script using the service account. In this workaround, the Web Apps created by Google Apps Script is used. The Web Apps can be used for executing the preserved functions of doGet and doPost from outside of Google. Ref In this workaround, this Web Apps is used for executing the various functions.

Using Until Expiration Time of Access Token Retrieved By googleapis for Python

Gists

When Google APIs are used with googleapis for Python, the client is obtained as follows.

creds = service_account.Credentials.from_service_account_file(service_account_credential_file, scopes=scopes)
service = build("drive", "v3", credentials=creds)

In this case, when the script is run, the access token is retrieved every time. But, the expiration time of the retrieved access token is 1 hour. Here, there might be the case that you want to use the access token until the expiration time. It is considered that effectively using the access token will lead to SDGs. In this post, I would like to introduce a sample script for using the access token until the expiration time.

Retrieving Access Token from Service Account using oauth2client and google-auth with Python

Gists

This is a sample script for retrieving the access token from the service account using oauth2client and google-auth with Python.

Sample script 1

Use oauth2client.

from oauth2client.service_account import ServiceAccountCredentials

SERVICE_ACCOUNT_FILE = "credentials.json"
SCOPES = ["https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
res = creds.get_access_token()
access_token = res.access_token

print(access_token)

Sample script 2

Use google-auth. In the current stage, this method might be general.

from google.oauth2 import service_account
import google.auth.transport.requests

SERVICE_ACCOUNT_FILE = "credentials.json"
SCOPES = ["https://www.googleapis.com/auth/drive"]

creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
request = google.auth.transport.requests.Request()
creds.refresh(request)
access_token = creds.token

print(access_token)

References

Using OnEdit trigger on Google Spreadsheet Created by Service Account

Gists

In the current stage, by the current specification, Google Apps Script cannot be directly run on Google Spreadsheet created by Service Account. But, there is a case in that we want to use the OnEdit trigger on the Spreadsheet that the service account is the owner. In this post, I would like to introduce the method for achieving this.

Recently, I published “Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users”. Here, this method is used.

Using Google API Client Library (gapi) for JavaScript with Service Account

Gists

This is a sample script for using Google API Client Library (gapi) for JavaScript with the service account. Unfortunately, in the current stage, gapi cannot directly use the service account. So, in this case, it is required to implement the script for retrieving the access token from the service account. In this report, I would like to introduce the method for using gapi with the service account using a Javascript library.

Javascript library - GetAccessTokenFromServiceAccount_js

Overview

This is a Javascript library to retrieve the access token from the Google Service Account. Ref

Description

I have already posted the sample script for retrieving the access token from the Google Service Account. Ref But, when I use this script, I thought that when this was published as the Javascript library, it will be useful. So I created this.

Install

<script src="getaccesstokengromserviceaccount_js.min.js"></script>

Or, using jsdelivr cdn

<script src="https://cdn.jsdelivr.net/gh/tanaikech/GetAccessTokenFromServiceAccount_js@master/getaccesstokengromserviceaccount_js.min.js"></script>

You can see the detail of this at https://github.com/tanaikech/GetAccessTokenFromServiceAccount_js

Retrieving Access Token using Service Account for PHP without using googleapis

Gists

This is a sample PHP script to retrieve the access token from Service Account of Google without using googleapis.

Sample script

<?php

$private_key = "-----BEGIN PRIVATE KEY-----\n###-----END PRIVATE KEY-----\n"; // private_key of JSON file retrieved by creating Service Account
$client_email = "###"; // client_email of JSON file retrieved by creating Service Account
$scopes = ["https://www.googleapis.com/auth/drive.readonly"]; // Sample scope

$url = "https://www.googleapis.com/oauth2/v4/token";
$header = array("alg" => "RS256", "typ" => "JWT");
$now = floor(time());
$claim = array(
	"iss" => $client_email,
	"sub" => $client_email,
	"scope" => implode(" ", $scopes),
	"aud" => $url,
	"exp" => (string)($now + 3600),
	"iat" => (string)$now,
);
$signature = base64_encode(json_encode($header, JSON_UNESCAPED_SLASHES)) . "." . base64_encode(json_encode($claim, JSON_UNESCAPED_SLASHES));
$b = "";
openssl_sign($signature, $b, $private_key, "SHA256");
$jwt = $signature . "." . base64_encode($b);
$curl_handle = curl_init();
curl_setopt_array($curl_handle, [
    CURLOPT_URL => $url,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_POST => true,
    CURLOPT_POSTFIELDS => array(
        "assertion" => $jwt,
        "grant_type" => "urn:ietf:params:oauth:grant-type:jwt-bearer"
    ),
]);
$res = curl_exec($curl_handle);
curl_close($curl_handle);
$obj = json_decode($res);
$accessToken = $obj -> {'access_token'};
print($accessToken . "\n");

// This is the script for testing the access token. The file list of the service account's Drive is retrieved using Drive API.
$curl_test = curl_init();
curl_setopt($curl_test, CURLOPT_URL, 'https://www.googleapis.com/drive/v3/files?access_token=' . $accessToken);
curl_setopt($curl_test, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($curl_test);
print($response);

?>

References

Safe-Uploading for Google Drive by HTML in External Server using Google Apps Script

Overview

This is a report for safe-uploading files to Google Drive by HTML put in the external server using Google Apps Script.

Description

When you want to make the user upload a file to your own Google Drive using the HTML put in the external server of Google side, when the file size is smaller than 50 MB, this can be achieved without using the access token. Ref (When the HTML is put in the internal server of Google side, you can also use google.script.run.) But, when the file size is over 50 MB, it is required to upload the file with the resumable upload. In this case, the access token is required to be used. In this case that the user uploads to your own Google Drive, when the access token is used in the upload, it is considered that this is the weak point of the security. In this report, I would like to propose the method for safe-uploading files to Google Drive by HTML put in the external server using Google Apps Script. Please think of this as one of several methods.

Retrieving Access Token for Service Account using Javascript

Gists

This is a sample script for retrieving the access token for Service Account using Javascript. The flow for using this script is as follows.

  1. At first, please create the Service Account and retrieve JSON file.
  2. Put Scopes, private_key and client_email to the script.
  3. Run the script.

Sample script

In this script, 2 libraries of jsencrypt and crypto-js are used.

<script src="https://cdnjs.cloudflare.com/ajax/libs/jsencrypt/3.0.0-rc.1/jsencrypt.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.0.0/crypto-js.min.js"></script>

<script>
  async function sample() {
    const private_key = "###"; // private_key of JSON file retrieved by creating Service Account
    const client_email = "###"; // client_email of JSON file retrieved by creating Service Account
    const scopes = ["https://www.googleapis.com/auth/drive.readonly"]; // Scopes

    const url = "https://www.googleapis.com/oauth2/v4/token";
    const header = { alg: "RS256", typ: "JWT" };
    const now = Math.floor(Date.now() / 1000);
    const claim = {
      iss: client_email,
      scope: scopes.join(" "),
      aud: url,
      exp: (now + 3600).toString(),
      iat: now.toString(),
    };
    const signature =
      btoa(JSON.stringify(header)) + "." + btoa(JSON.stringify(claim));
    const sign = new JSEncrypt();
    sign.setPrivateKey(private_key);
    const jwt =
      signature + "." + sign.sign(signature, CryptoJS.SHA256, "sha256");
    const params = {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        assertion: jwt,
        grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer",
      }),
    };
    const obj = await fetch(url, params)
      .then((res) => res.json())
      .catch((err) => console.log(err));
    console.log(obj);
  }

  sample();
</script>

If the access token retrieved at above is used for retrieving file list, the sample script is as follows.

Retrieving Access Token using Service Account for Node.js without using googleapis

Gists

This is a sample Node.js script to retrieve access token from Service Account of Google without using googleapis.

const cryptor = require('crypto');
const request = require('request');

const privateKey = "###"; // private_key of JSON file retrieved by creating Service Account
const clientEmail = "###"; // client_email of JSON file retrieved by creating Service Account
const scopes = ["https://www.googleapis.com/auth/drive.readonly"]; // Sample scope

const url = "https://www.googleapis.com/oauth2/v4/token";
const header = {
  alg: "RS256",
  typ: "JWT",
};
const now = Math.floor(Date.now() / 1000);
const claim = {
  iss: clientEmail,
  scope: scopes.join(" "),
  aud: url,
  exp: (now + 3600).toString(),
  iat: now.toString(),
};

const signature = Buffer.from(JSON.stringify(header)).toString('base64') + "." + Buffer.from(JSON.stringify(claim)).toString('base64');

var sign = cryptor.createSign('RSA-SHA256');
sign.update(signature);
const jwt = signature + "." + sign.sign(privateKey, 'base64');

request({
  method: "post",
  url: url,
  body: JSON.stringify({
    assertion: jwt,
    grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer",
  }),
}, (err, res, body) => {
  if (err) {
    console.log(err);
    return;
  }
  console.log(body);
});

Retrieving Access Token for Service Account using Google Apps Script

Gists

Updated on June 22, 2024

This is a sample script for retrieving the access token for Service Account using Google Apps Script. The flow for using this script is as follows.

Usage

  1. Create the Service Account and retrieve JSON file.
  2. Put Scopes, private_key and client_email as an object.
  • If you want to use impersonate email, please set the value of impersonate_email.
  1. Run the script.
/**
 * ### Description
 * Get access token from service account.
 * ref: https://tanaikech.github.io/2018/12/07/retrieving-access-token-for-service-account-using-google-apps-script/
 *
 * @param {Object} object Object including private_key, client_email, impersonate_email.
 * @param {String} object.private_key
 * @param {String} object.client_email
 * @param {String} object.impersonate_email
 * @param {Array} object.scopes
 * @returns {String} Access token.
 */
function getAccessTokenFromServiceAccount_(object) {
  const { private_key, client_email, impersonate_email = "", scopes = [] } = object;
  const url = "https://www.googleapis.com/oauth2/v4/token";
  const header = { alg: "RS256", typ: "JWT" };
  const now = Math.floor(Date.now() / 1000);
  const claim = { iss: client_email, scope: scopes.join(" "), aud: url, exp: (now + 3600).toString(), iat: now.toString() };
  if (impersonate_email != "") {
    claim.sub = impersonate_email;
  }
  const signature = Utilities.base64Encode(JSON.stringify(header)) + "." + Utilities.base64Encode(JSON.stringify(claim));
  const jwt = signature + "." + Utilities.base64Encode(Utilities.computeRsaSha256Signature(signature, private_key));
  const params = { payload: { assertion: jwt, grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer" } };
  const res = UrlFetchApp.fetch(url, params);
  const { access_token } = JSON.parse(res.getContentText());
  return access_token;
}


// Please run this script.
function main() {
  const object = {
    private_key: "-----BEGIN PRIVATE KEY-----\n###-----END PRIVATE KEY-----\n",
    client_email: "###",
    // impersonate_email: "###", // If you want to use an impersonate email, please set this.
    scopes: ["###", "###",,,],
  };
  const accessToken = getAccessTokenFromServiceAccount_(object);
  console.log(accessToken);
}

Sample script

When you can obtain the access token by the above script, you can also use the following sample script. The following sample script retrieves the file list of the drive of the service account with Method: files.list of Drive API v3.