Executing App Script functions in Firebase Cloud Functions

README.MD

If you are using Firebase on a Spark (free) plan, you can only make outbound networking requests to Google services. Unfortunately, that does not include Web app deployed from App Script (script.google.com/macros/s/.../exec). (Stackoverflow #43330192)

This is a crude example showing how to execute Apps Script using the scripts.run method of Apps Script API.

In this example, the Firebase app does not store the user's OAuth id token, does not validate the access token (e.g. the access token is issued for your project, not someone else's), and does not store the refresh token (access token will expire 60min after issue if not refreshed). For production, these issues must be resolved.

appscript.js

/* ***************************************
   firebase_project/functions/appscript.js
   *************************************** */

const functions = require('firebase-functions');
const axios = require('axios');
const querystring = require('querystring');

/*
  See
  https://developers.google.com/apps-script/api/reference/rest/v1/scripts/run
  for API reference
*/

/*
  NOTE:
  In order to use AppScript REST API,
  1. The script and your Firebase project must belong to the same
     project
  2. You must deploy the script as API executable
     1. In the script editor, select "Publish" > "Deploy as API executable..."
     2. Set "Who has access to the script" to "Anyone"

  NOTE
  You do not need to "share" the script (e.g. you do not
  to select "File" > "Share"). So you are granting the user
  to execute your script but not to view your code.
*/

const gasFunctions = {
  "hello_world" : {
    "scriptId": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "function": "helloWorld"
  },
  "function2" : {
    "scriptId": "xxxxxxxxx",
    "function": "function2"
  }
};

exports.run = function(accessToken, fnName, fnParams) {

  const fn = gasFunctions[fnName];

  if (!fn) {
    throw "Cannot find function: " + fnName;
  }

  fnName = fn["function"];
  if (!(fnParams instanceof Array)) { fnParams = [fnParams]; }

  const url = "https://script.googleapis.com/v1/scripts/" +
              fn["scriptId"] + ":run";

  const config = {
      headers: {
          "Authorization": 'Bearer ' + accessToken,
          "content-type": 'application/json'
      }
  };

  const body = {
      "parameters": fnParams,
      "function": fnName
  };

  console.log("Executing Apps Script function", fnName)
  return axios
    .post(url, body, config)
    .then(res => {
          console.log("Apps Script result", res.data);
          return {
            "status": res.status,
            "access_token": accessToken,
            "data": res.data.done?res.data.response.result:""
          }
        })
}

code.gs

/* ***************************************
   script_project/code.gs
   *************************************** */

function doGet(e) {
  var message = "Hello World!";
  if ( (typeof(e) !== "undefined") &&  (e.parameter.message) ) {
    message = e.parameter.message;
  }
  return ContentService.createTextOutput("Echo: " + message);
}

function doPost(e) {
  var message = "Hello World!";
  if ( (typeof(e) !== "undefined") &&  (e.parameter.message) ) {
    message = e.parameter.message;
  }
  return ContentService.createTextOutput("Echo: " + message);
}

function helloWorld(message) {
  if (!message) {
     message = "Hello World!";
  }
  return "Echo: " + message;
}

firebase.json

/* ***************************************
   firebase_project/firebase.json
   *************************************** */

{
  "functions": {
    "apredeploy": [
      "npm --prefix \"$RESOURCE_DIR\" run lint"
    ]
  },
  "hosting": {
    "public": "public",
    "rewrites": [
      { "source": "/oauthcallback", "function": "main-oauthCallback" },
      { "source": "/execAppScript", "function": "main-execAppScript" }
    ],
    "ignore": [
      "firebase.json",
      "**/.*",
      "**/node_modules/**"
    ]
  }
}

index.js

/* ***************************************
   firebase_project/functions/index.js
   *************************************** */

// Sample code for executing AppScript
const functions = require('firebase-functions');
const axios = require('axios');
const querystring = require('querystring');

const appscript = require("./appscript");

exports.main = {
  execAppScript: functions.https.onRequest((req,res) => {execAppScript(req,res)}),
  oauthCallback: functions.https.onRequest((req,res) => {oauthCallback(req,res)})
}

exports.appscript = appscript;


/*
 Callback URLs for OAuth result
 */
const oauthCallbackUrl = "https://" +
                        process.env.GCLOUD_PROJECT +
                        ".firebaseapp.com/oauthcallback";

/*
 OAuth 2.0 client IDs
 1. visit https://console.developers.google.com
 2. select your project
 3. select APIs & Services > Credentials
 4. create a OAuth client ID for Web application if you don't have one
 5. select your Web client ID, and add your OAuth callback URI
 (e.g. "https://<your-project-name>.firebaseapp.com/oauthcallback" )
 6. download the JSON file of the client
 7. rename and save it to the same folder as your code files
 */
const keys = require('./xxxxxxxxxxxxxxxxx.json');


/*
 scopes required to execute the script
 1. In the script editor, select "File" > "Project properties"
 2. Select the "Scopes" tab
 3. Add all scopes to the list
 */
const scriptScopes = [
                      "https://www.googleapis.com/auth/userinfo.email"
                      ];


function execAppScript(request, response) {

  request.allparams = Object.assign(request.query, request.body);
  console.log(request.path);
  console.log("params", request.allparams);
  //console.log("headers", request.headers);

  var accessToken = request.allparams.access_token;
  var fnName = request.allparams["function"];
  var fnParams = request.allparams["params"];

  if (!fnName) {
    fnName = "hello_world";
    fnParams = ["Beep beep beep!"];
  }

  if (!accessToken) {

    var opts = {
      redirect_uri : oauthCallbackUrl,
      response_type: "code",
      client_id: keys.web.client_id,
      scope: scriptScopes.join(" "),
      access_type: "offline",
      state: JSON.stringify( { "function":fnName, "params": fnParams } )
    };
    var authUrl = "https://accounts.google.com/o/oauth2/v2/auth?" +
                  querystring.stringify(opts);
    response.writeHead(307, { 'Location': authUrl });
    response.end();

  } else {

    appscript.run(accessToken, fnName, fnParams)
      .then(res => {
          response.set("access_token", accessToken)
          response.set("Access-Control-Allow-Origin", "*");
          response.set("Access-Control-Allow-Methods", "GET, POST");
          response.status(200).end(res.data);
        })
      .catch(err => {
          // handle axios errors
          if (err.response) {
            console.error(err.response.data);
            console.error(err.response.status);
            console.error(err.response.headers);
          } else if (err.request) {
            console.error(err.request)
          } else {
            console.error("Error", err.message);
          }
          console.error(err.config);
          response.status(500).end();
      });
  }

}


function oauthCallback (request, response) {

  request.allparams = Object.assign(request.query, request.body);
  console.log(request.path);
  console.log("params", request.allparams);
  //console.log("headers", request.headers);

  var auth_code = request.allparams.code;

  if (!auth_code) {
    console.error("no auth code");
    response.status(500).end();
    return null;
  }

  var fnName;
  var fnParams;

  try {
    var state = JSON.parse( request.allparams.state );
    fnName = state["function"];
    fnParams = state["params"];
  } catch (err) {
    console.error("JSON parse error");
    response.status(500).end();
    return null;
  }

  if (!fnName) {
    fnName = "hello_world";
    fnParams = ["Honk honk honk!"];
  }

  // Exchange authentication code for access token
  return getAccessToken(auth_code)
      .then(accessToken => {
          return appscript.run(accessToken, fnName, fnParams)
      })
      .then(res => {
          response.set("access_token", res.accessToken)
          response.set("Access-Control-Allow-Origin", "*");
          response.set("Access-Control-Allow-Methods", "GET, POST");
          response.status(res.status).end(res.data);
      })
      .catch(err => {
          // handle axios errors
          if (err.response) {
            console.error(err.response.data);
            console.error(err.response.status);
            console.error(err.response.headers);
          } else if (err.request) {
            console.error(err.request)
          } else {
            console.error("Error", err.message);
          }
          console.error(err.config);
          response.status(500).end();
      });

}


function getAccessToken(auth_code) {
  console.log("token exchange, send", auth_code);

  const url = "https://www.googleapis.com/oauth2/v4/token";
  const config = { timeout: 10000 };
  const body = {
    code: auth_code,
    redirect_uri : oauthCallbackUrl,
    client_id: keys.web.client_id,
    client_secret: keys.web.client_secret,
    scope: "",
    grant_type: "authorization_code"
  };

  return axios
    .post(url, querystring.stringify(body), config)
    .then(res => {
        console.log("token exchange, get", res.data);
        return res.data.access_token;
    })
}