Formula Works in Google App Script Console, But Returns Authentication Error in Spreadsheets

Question

I have created a custom formula for one of my spreadsheets. It gets two Date parameters and returns the Total Unique Event count of a specific Google Analytics event. When I test my code, it works perfectly in the Google Apps Script. But when I try to use the same formula in the associated Spreadsheet, it causes the following error:

GoogleJsonResponseException: API call to analytics.data.ga.get failed with error: 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. (line 13).

Here is my code:

var tableId = 'ga:78467590';

function test(){
  USE_CLICK_COUNT("2020-08-31", "2020-09-06");
}

function USE_CLICK_COUNT(startDate, endDate){ 
  var optArgs = {
    'dimensions':'ga:eventLabel',
    'filters':'ga:eventLabel=~^UseClick'
  };
  
  var results = Analytics.Data.Ga.get(tableId, startDate, endDate, 'ga:uniqueEvents', optArgs);
  Logger.log(results.getTotalsForAllResults());
  return results.getTotalsForAllResults();

}

When I run the test() function, it returns and logs {ga:uniqueEvents=13} - this is the number I'm looking for.

I have checked APIs and configuration details over and over again and can't figure out what I'm missing. Please see the attachments.

Answer 1

Issue and workaround:

I thought that your situation is the same with this thread. Unfortunately, in the current stage, Google APIs of Advanced Google services cannot be directly used with the custom function. Because the access token cannot be retrieved at the custom function. It seems that this is the current specification. So as the workaround for achieving your goal, it uses Web Apps created by Google Apps Script.

But I thought that the script of your question is a bit different from this thread. So when a sample script is proposed, it might be useful for you. So I would like to introduce the sample script for achieving your goal.

1. Prepare script.

Please copy and paste the following script to the script editor and save it.

Sample script:

// This is your script.
var tableId = 'ga:78467590';

function test(){
  USE_CLICK_COUNT("2020-08-31", "2020-09-06");
}

function USE_CLICK_COUNT(startDate, endDate){ 
  var optArgs = {
    'dimensions':'ga:eventLabel',
    'filters':'ga:eventLabel=~^UseClick'
  };
  var results = Analytics.Data.Ga.get(tableId, startDate, endDate, 'ga:uniqueEvents', optArgs);
  Logger.log(results.getTotalsForAllResults());
  return results.getTotalsForAllResults();
}

// I added below script.
const key = "samplekey"; // This is used as the key for running the Web Apps.

// Web Apps using as the wrapper.
function doGet(e) {
  if (e.parameter.key === key) {
    const res = USE_CLICK_COUNT(e.parameter.startDate, e.parameter.endDate);
    return ContentService.createTextOutput(JSON.stringify(res));
  }
  return ContentService.createTextOutput("Error.");
}

// This is used as the custom function.
function RUN_USE_CLICK_COUNT(startDate, endDate) {
  const url = `https://script.google.com/macros/s/###/exec?startDate=${startDate}&endDate=${endDate}&key=${key}`;  // Please set the URL of Web Apps after you set the Web Apps.
  return UrlFetchApp.fetch(url).getContentText();
}

2. Deploy Web Apps.

1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".

2. Select "Me" for "Execute the app as:".

   • By this, the script is run as the owner.

3. Select "Anyone, even anonymous" for "Who has access to the app:".

   • In this case, no access token is required to be request. I think that I recommend this setting for testing this workaround.
   • Of course, you can also use the access token. But, in this case, when the access token is used, this sample script cannot be directly used as the custom function. So in this answer, I proposed to use the key for running the Web Apps script. But if you want to use the access token, I think that it will be achieved using PropertiesService.

4. Click "Deploy" button as new "Project version".

5. Automatically open a dialog box of "Authorization required".

   1. Click "Review Permissions".
   2. Select own account.
   3. Click "Advanced" at "This app isn't verified".
   4. Click "Go to ### project name ###(unsafe)"
   5. Click "Allow" button.

6. Click "OK".

7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.

   • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

8. Please set the URL of https://script.google.com/macros/s/###/exec to url of above script. And please redeploy Web Apps. By this, the latest script is reflected to the Web Apps. So please be careful this.

4. Test this workaround.

Please put =RUN_USE_CLICK_COUNT("2020-08-31", "2020-09-06") to a cell. By this, the values of "2020-08-31", "2020-09-06" are sent to the Web Apps and the values from USE_CLICK_COUNT are returned.

Note:

• When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

References:

• Custom Functions in Google Sheets
• Web Apps
• Taking advantage of Web Apps with Google Apps Script