tanaikech/submit.md

## submit.md

      
    Raw
  

              submit.md
            
          
    Executing Google Apps Script with Service Account


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.
Principle of this workaround


Unfortunately, the service account cannot directly execute Google Apps Script by the current specification. However, fortunately, the Web Apps can be accessed by the service account. This workaround uses this situation. The key factor for this workaround is to use the Web Apps deployed as Execute as: Me and Who has access to the app: Anyone with Google account. The service account is used for firing the Web Apps and the script of Google Apps Script is run as the owner of the Google Apps Script project (It's you.). You can see the figure of this as shown in the above figure.
For example, when the Web Apps deployed as Execute as: Who has access and Who has access to the app: Anyone with Google account is used, the service account cannot access the Web Apps.
Of course, it is considered that it is possible to use the Web Apps as the wrapper API of Google Apps Script API. But, in this case, the preparation process is a bit complicated. So, in this report, I proposed the above method.
Usage

In this case, one Google Apps Script project as the server and a script as the client side are used.
1. Create service account

In order to execute the Google Apps Script using the service account, please create your service account.
2. Server-side

2-1. Create a Google Apps Script project

On the server side, the Google Apps Script project is used. So, as a sample, please create a new Google Spreadsheet and open the script editor of Google Apps Script. This Google Apps Script project is used as the server side. Also, the functions in this project are executed by selecting the function.
Of course, you can use the standalone Google Apps Script project. But, in this case, in order to use the sample script, the container-bound script of Google Spreadsheet is used.
2-2. Share Google Apps Script project with a service account

In this case, please share the Google Spreadsheet with the email of your service account.
2-3. Prepare a sample script for Web Apps

This is used as a server. Please copy and paste the following script to the opened script editor and save the script.
// Sample function. This is a sample user's function.
function setValue({ range, values }) {
  SpreadsheetApp.getActive().getRange(range).setValues(values);
  return "ok";
}

//
// Below functions are used for executing the script using Web Apps.
//
function process_(method, { parameter, postData }) {
  const lock = LockService.getScriptLock();
  if (lock.tryLock(350000)) {
    try {
      const { functionName } = parameter;
      let response = "No function.";
      if (functionName && !["doGet", "doPost"].includes(functionName)) {
        let args;
        if (method == "get") {
          const { arguments } = parameter;
          if (arguments) {
            args = JSON.parse(arguments);
          }
        } else if (method == "post" && postData) {
          const { contents } = postData;
          if (contents) {
            args = JSON.parse(contents);
          }
        }
        const res = this[functionName](args);
        response = typeof res == "object" ? JSON.stringify(res) : res;
      }
      return ContentService.createTextOutput(response);
    } catch ({ stack }) {
      return ContentService.createTextOutput(stack);
    } finally {
      lock.releaseLock();
    }
  } else {
    return ContentService.createTextOutput(Timeout);
  }
}

const doGet = (e) => process_("get", e);
const doPost = (e) => process_("post", e);

// This function is used for retrieving the Web Apps URL.
function getUrl() {
  console.log(ScriptApp.getService().getUrl());
}
In this script, the function of setValue is the sample script of the user's functions. Please add your script to this Google Apps Script project.
The functions doGet, doPost, getUrl, and process_ are used for executing the user's functions.
2-4. Deploy Web Apps

The detailed information can be seen in the official document.

On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
Please click "Select type" -> "Web App".
Please input the information about the Web App in the fields under "Deployment configuration".
Please select "Me" for "Execute as".
Please select "Anyone with Google account" for "Who has access".
Please click "Deploy" button.
Please run the function getUr() of the above script. By this, you can see your Web Apps URL. It’s like https://script.google.com/macros/s/###/dev. Please copy this URL. This URL is used on the client side. If the URL is not dev, please retrieve it on the script editor.


In this case, the Web Apps is accessed as the developer mode with the endpoint of https://script.google.com/macros/s/###/dev. So, when you modify the Google Apps Script, it is not required to redeploy Web Apps. The latest script is always used when the Web Apps is accessed with the endpoint of https://script.google.com/macros/s/###/dev.


As additional information, when Execute as: Me is changed to Execute as: Anyone with Google account, the service account cannot access the Web Apps. Please be careful about this.


By this flow, the setting of the server side was finished.
3. Client-side

Here, as the sample clients, I would like to introduce the following 4 sample scripts. Please set your Web Apps URL retrieved by getUr() to the following scripts, and test it.
In order to access the Web Apps in the developer mode (https://script.google.com/macros/s/###/dev), the access token is used.
In the current stage, it seems that the access token can be used for the request header and the query parameter. Ref If you use the access token to the request header and an error occurs, please try to use it to the query parameter. For example, when the fetch API of Javascript is used, I have a case where an error occurs. At that time, the issue could be removed by using the access token to the query parameter instead of the request header.
3-1. Google Apps Script

// Ref: https://tanaikech.github.io/2018/12/07/retrieving-access-token-for-service-account-using-google-apps-script/
function getAccessToken_({ private_key, client_email, scopes }) {
  var url = "https://www.googleapis.com/oauth2/v4/token";
  var header = { alg: "RS256", typ: "JWT" };
  var now = Math.floor(Date.now() / 1000);
  var claim = {
    iss: client_email,
    scope: scopes.join(" "),
    aud: url,
    exp: (now + 3600).toString(),
    iat: now.toString(),
  };
  var signature =
    Utilities.base64Encode(JSON.stringify(header)) +
    "." +
    Utilities.base64Encode(JSON.stringify(claim));
  var jwt =
    signature +
    "." +
    Utilities.base64Encode(
      Utilities.computeRsaSha256Signature(signature, private_key)
    );
  var params = {
    payload: {
      assertion: jwt,
      grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer",
    },
  };
  var res = UrlFetchApp.fetch(url, params).getContentText();
  var { access_token } = JSON.parse(res);
  return access_token;
}

function sample() {
  const private_key =
    "-----BEGIN PRIVATE KEY-----\n###-----END PRIVATE KEY-----\n"; // Please set your value.
  const client_email = "###"; // Please set your value.

  const scopes = ["https://www.googleapis.com/auth/drive.readonly"];
  const accessToken = getAccessToken_({ private_key, client_email, scopes });

  const functionName = "setValue";
  const webAppsUrl = "https://script.google.com/macros/s/###/dev";

  // POST method
  const arguments1 = {
    range: "'Sheet1'!A1:A2",
    values: [["sample value 1"], ["sample value 2"]],
  };
  const res1 = UrlFetchApp.fetch(`${webAppsUrl}?functionName=${functionName}`, {
    headers: { Authorization: "Bearer " + accessToken },
    payload: JSON.stringify(arguments1),
  });
  console.log(res1.getContentText());

  // GET method
  const arguments2 = {
    range: "'Sheet1'!B1:B2",
    values: [["sample value 3"], ["sample value 4"]],
  };
  const res2 = UrlFetchApp.fetch(
    `${webAppsUrl}?functionName=${functionName}&arguments=${encodeURIComponent(
      JSON.stringify(arguments2)
    )}`,
    { headers: { Authorization: "Bearer " + accessToken } }
  );
  console.log(res2.getContentText());
}
3-2. golang

package main

import (
	"bytes"
	"encoding/json"
	"fmt"
	"io"
	"log"
	"net/http"
	"net/url"

	"github.com/tanaikech/go-gettokenbyserviceaccount"
)

// GET method
func getMethod(accessToken, webAppsURL string) {
	dataStr := struct {
		Range  string          `json:"range"`
		Values [][]interface{} `json:"values"`
	}{
		"'Sheet1'!B1:B2",
		[][]interface{}{{"sample value 3"}, {"sample value 4"}},
	}
	data, _ := json.Marshal(dataStr)
	u, err := url.Parse(webAppsURL)
	if err != nil {
		log.Fatal(err)
	}
	q := u.Query()
	q.Set("functionName", "setValue")
	q.Set("arguments", string(data))
	u.RawQuery = q.Encode()
	req, err := http.NewRequest("GET", u.String(), nil)
	if err != nil {
		log.Fatal(err)
	}
	req.Header.Set("Authorization", "Bearer "+accessToken)
	client := &http.Client{}
	res2, err := client.Do(req)
	if err != nil {
		log.Fatal(err)
	}
	defer res2.Body.Close()
	b, err := io.ReadAll(res2.Body)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(string(b))
}

// POST method
func postMethod(accessToken, webAppsURL string) {
	url := webAppsURL + "?functionName=setValue"
	dataStr := struct {
		Range  string          `json:"range"`
		Values [][]interface{} `json:"values"`
	}{
		"'Sheet1'!A1:A2",
		[][]interface{}{{"sample value 1"}, {"sample value 2"}},
	}
	data, _ := json.Marshal(dataStr)
	req, err := http.NewRequest(
		"POST",
		url,
		bytes.NewReader(data),
	)
	if err != nil {
		log.Fatal(err)
	}
	req.Header.Set("Content-Type", "application/json")
	req.Header.Set("Authorization", "Bearer "+accessToken)
	client := &http.Client{}
	res2, err := client.Do(req)
	if err != nil {
		log.Fatal(err)
	}
	defer res2.Body.Close()
	b, err := io.ReadAll(res2.Body)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(string(b))
}

func main() {
	privateKey := "-----BEGIN PRIVATE KEY-----\n###-----END PRIVATE KEY-----\n" // Please set your value.
	clientEmail := "###" // Please set your value.

	scopes := "https://www.googleapis.com/auth/drive.readonly"
	impersonateEmail := ""
	res1, err := gettokenbyserviceaccount.Do(privateKey, clientEmail, impersonateEmail, scopes)
	if err != nil {
		log.Fatal(err)
	}
	accessToken := res1.AccessToken
	webAppsURL := "https://script.google.com/macros/s/###/dev"
	postMethod(accessToken, webAppsURL)
	getMethod(accessToken, webAppsURL)
}
3-3. Node.js

const https = require("https");
const { google } = require("googleapis");

const auth = new google.auth.JWT({
  keyFile: "serviceAccount_credential.json", // Please set your value.

  scopes: ["https://www.googleapis.com/auth/drive.readonly"],
});

// POST method
function postMethod({ token, webAppsUrl, functionName }) {
  const arguments = {
    range: "'Sheet1'!A1:A2",
    values: [["sample value 1"], ["sample value 2"]],
  };
  const options = {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: "Bearer " + token,
    },
  };
  const req1a = https.request(
    `${webAppsUrl}?functionName=${functionName}`,
    options,
    (res) => {
      const chunks = [];
      const req1b = https.request(res.headers["location"], (res2) => {
        res2
          .on("data", (chunk) => chunks.push(Buffer.from(chunk)))
          .on("end", () => console.log(Buffer.concat(chunks).toString()));
      });
      req1b.on("error", (err) => console.log(err));
      req1b.end();
    }
  );
  req1a.write(JSON.stringify(arguments));
  req1a.on("error", (err) => console.log(err));
  req1a.end();
}

// GET method
function getMethod({ token, webAppsUrl, functionName }) {
  const arguments = {
    range: "'Sheet1'!B1:B2",
    values: [["sample value 3"], ["sample value 4"]],
  };
  const options = {
    method: "GET",
    headers: { Authorization: "Bearer " + token },
  };
  const req2a = https.request(
    `${webAppsUrl}?functionName=${functionName}&arguments=${JSON.stringify(
      arguments
    )}`,
    options,
    (res) => {
      const chunks = [];
      const req2b = https.request(res.headers["location"], (res2) => {
        res2
          .on("data", (chunk) => chunks.push(Buffer.from(chunk)))
          .on("end", () => console.log(Buffer.concat(chunks).toString()));
      });
      req2b.on("error", (err) => console.log(err));
      req2b.end();
    }
  );
  req2a.on("error", (err) => console.log(err));
  req2a.end();
}

async function main() {
  const { token } = await auth.getAccessToken();
  const webAppsUrl = "https://script.google.com/macros/s/###/dev";
  const functionName = "setValue";
  postMethod({ token, webAppsUrl, functionName });
  getMethod({ token, webAppsUrl, functionName });
}

main();
3-4. Python

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


def getAccessToken():
    SERVICE_ACCOUNT_FILE = "serviceAccount_credential.json" # Please set your value.

    creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=["https://www.googleapis.com/auth/drive.readonly"])
    creds.refresh(google.auth.transport.requests.Request())
    return creds.token

# Please set your Web Apps URL.
webApps_url = "https://script.google.com/macros/s/###/dev"

access_token = getAccessToken()

# POST method
functionName = "setValue"
arguments = {"range": "'Sheet1'!A1:A2", "values": [["sample value 1"], ["sample value 2"]]}
url = f'{webApps_url}?functionName={functionName}'
res = requests.post(url, json.dumps(arguments), headers={"Authorization": "Bearer " + access_token})
print(res.text)

# GET method
functionName = "setValue"
arguments = {"range": "'Sheet1'!B1:B2", "values": [["sample value 3"], ["sample value 4"]]}
url = f'{webApps_url}?functionName={functionName}&arguments={json.dumps(arguments)}'
res = requests.get(url, json.dumps(arguments), headers={"Authorization": "Bearer " + access_token})
print(res.text)
4. Testing

All sample scripts obtain the same results.

Values of "sample value 1", "sample value 2" are put into cells "A1:A2" by the POST method.
Values of "sample value 3", "sample value 4" are put into cells "A1:A2" by the GET method.

Reference


Taking advantage of Web Apps with Google Apps Script