Public Google Script doesn't run from a chrome extension

Question

I have a Google script which modifies the content of a google spreadsheet (it's not the final script of course), but I have a problem to run this simple script from a google chrome extension. Here is the script attached to my spreadsheet :

function insertData(parameters) {
  var spreadsheet = SpreadsheetApp.openByUrl(THE_URL_OF_THE_SPREADSHEET)
  spreadsheet.getRange('A5').activate();
  spreadsheet.getCurrentCell().setValue(parameters.data1);
  spreadsheet.getRange('B5').activate();
  spreadsheet.getCurrentCell().setValue(parameters.data2);
}

I deployed this script both as a web app (execute as me + access to everyone, even anonymous) and as an executable API (access to anyone).

Then I tried this JS script to run my google script, from a google chrome extension, using this code that I got from an google chrome extension example:

sendDataToExecutionAPICallback: function() {
    post({  'url': 'https://script.googleapis.com/v1/scripts/' + SCRIPT_ID + ':run',
            'callback': obj.executionAPIResponse,
            'token': 'MY_GENERATED_TOKEN'
            'request': {
                'function': 'insertData',
                'parameters': {                 
                    'data1': 'ok1 from script',
                    'data2': 'ok2 from script'
                },
                'devMode': true
            }
        });
}, 

executionAPIResponse: function(response){
    var obj = this;
    var info;
    if (response.response.result.status == 'ok'){
        info = 'Data has been entered';
    } else {
        info = 'Error...';
    }
    obj.displayMessage(info);
}

And I have this post function :

function post(options) {
    var xhr = new XMLHttpRequest();
    xhr.onreadystatechange = function() {
        if (xhr.readyState === 4 && xhr.status === 200) {
            // JSON response assumed. Other APIs may have different responses.
            options.callback(JSON.parse(xhr.responseText));
        } else if(xhr.readyState === 4 && xhr.status !== 200) {
            console.log('post', xhr.readyState, xhr.status, xhr.responseText);
        }
    };
    xhr.open('POST', options.url, true);
    // Set standard Google APIs authentication header.
    xhr.setRequestHeader('Authorization', 'Bearer ' + options.token);
    xhr.send(JSON.stringify(options.request));
}

And when I call the sendDataToExecutionAPICallback function, I got this auth error:

"error": {
    "code": 401,
    "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
    "status": "UNAUTHENTICATED"
}

EDIT1: After generating a token, and added it to my code, I have this error:

 "error": {
    "code": 403,
    "message": "The caller does not have permission",
    "status": "PERMISSION_DENIED"
  }

Answer 1

I would like to propose two methods. Please choose one of them.

Method 1: Execution API (Method: scripts.run of Apps Script API)

In order to run the functions using Execution API, please carry out the following flow.

1. Create a project (standalone or bound script).
2. Copy and paste your GAS script to the project. In this case, your GAS script was not modified.
3. Deploy API executable. As a sample, choose "Only myself" as "Who has access to the script"
4. Enable Apps Script API at API console.
5. Using the save button, save the project on the script editor. This is an important point. By this, the scripts are reflected to the deployed execution API.
6. Retrieve access token from client ID and client secret of this project.
   • Please include https://www.googleapis.com/auth/drive, https://www.googleapis.com/auth/drive.scripts, https://www.googleapis.com/auth/spreadsheets in the scopes. https://www.googleapis.com/auth/drive might not be used for this situation.

Please use the retrieved access token to the following modified script.

sendDataToExecutionAPICallback: function() {
  post({
    'url': 'https://script.googleapis.com/v1/scripts/' + SCRIPT_ID + ':run',
    'callback': obj.executionAPIResponse,
    'token': 'MY_GENERATED_TOKEN',
    'request': {
      'function': 'insertData',
      'parameters': [{
        'data1': 'ok1 from script',
        'data2': 'ok2 from script',
      }],
      'devMode': true,
    }
  });
},

executionAPIResponse: function(response) {
  var info;
  if (!response.error) {
    info = 'Data has been entered';
  } else {
    info = 'Error...';
  }
  obj.displayMessage(info);
},

function post(options) {
  var xhr = new XMLHttpRequest();
  xhr.onreadystatechange = function() {
    if (xhr.readyState === 4 && xhr.status === 200) {
      // JSON response assumed. Other APIs may have different responses.
      options.callback(JSON.parse(xhr.responseText));
    } else if(xhr.readyState === 4 && xhr.status !== 200) {
      console.log('post', xhr.readyState, xhr.status, xhr.responseText);
    }
  };
  xhr.open('POST', options.url, true);
  // Set standard Google APIs authentication header.
  xhr.setRequestHeader('Authorization', 'Bearer ' + options.token);
  xhr.setRequestHeader('Content-Type', 'application/json');
  xhr.send(JSON.stringify(options.request));
}

Note :

• In my environment, this modified script works. But if in your environment, this didn't work, could you please try the method 2?

Method 2: Web Apps

From your question and comments, I'm worried that the access token might not be able to be used. So I would like to also propose the method using Web Apps. In this case, you can run the function without the access token. But the password key to run is used at the payload.

When you use this, please deploy Web Apps as "Execute the app as:" : Me and "Who has access to the app:": Anyone, even anonymous. If you want to know the detail information of Web Apps, please check here.

In this sample script, it doesn't use xhr.setRequestHeader('Authorization', 'Bearer ' + options.token); of post().

The modified script is as follows. Please put GAS to the script editor which deploys Web Apps.

function doPost(e) {
  var p = JSON.parse(e.postData.contents);
  if (p.password == 'samplePassword') {
    insertData(p.parameters);
    return ContentService.createTextOutput("ok");
  } else {
    return ContentService.createTextOutput("error");
  }
}

function insertData(parameters) {
  var spreadsheet = SpreadsheetApp.openByUrl(THE_URL_OF_THE_SPREADSHEET);
  spreadsheet.getRange('A5').activate();
  spreadsheet.getCurrentCell().setValue(parameters.data1);
  spreadsheet.getRange('B5').activate();
  spreadsheet.getCurrentCell().setValue(parameters.data2);
}

sendDataToExecutionAPICallback: function() {
  post({
    'url': 'https://script.google.com/macros/s/#####/exec', // URL of Web Apps
    'callback': obj.executionAPIResponse,
    'request': {
      'parameters': {
        'data1': 'ok1 from script',
        'data2': 'ok2 from script'
      },
      'password': 'samplePassword',
    }
  });
}, 
executionAPIResponse: function(response) {
  var info;
  if (response == 'ok') {
    info = 'Data has been entered';
  } else {
    info = 'Error...';
  }
  obj.displayMessage(info);
},

function post(options) {
  var xhr = new XMLHttpRequest();
  xhr.onreadystatechange = function() {
    if (xhr.readyState === 4 && xhr.status === 200) {
      // JSON response assumed. Other APIs may have different responses.
      options.callback(xhr.responseText);
    } else if(xhr.readyState === 4 && xhr.status !== 200) {
      console.log('post', xhr.readyState, xhr.status, xhr.responseText);
    }
  };
  xhr.open('POST', options.url, true);
  // Set standard Google APIs authentication header.
  xhr.send(JSON.stringify(options.request));
}

Note :

• If you use Web Apps, after copied and pasted the GAS script to the script editor, please redeploy Web Apps as a new version. By this, the latest script is reflected.

In my environment, I could confirm that both methods worked.