Field Tweaker Analytics Update, GAS, Automation-Script: little report and Issues

Olpy_Acaflo · ‎Aug 15, 2020

My References:
@Bill.French FIELD TWEAKER

My Report:

Hello @Bill.French & Community Scripters,

Inspired by

//
        // update analytics
        //

        // establish the cgp web service endpoint
        let gcpUrl = "https://script.google.com/macros/s/AKfycbxGO0QVOYEiiBkseEh3AUSnHFMpS0OXidRtAUMTNIjj_EaFdBo/exec";

        let payload = {
            "userid"    : session.currentUser.id,
            "username"  : session.currentUser.name,
            "useremail" : session.currentUser.email
        }

        let postOptions = {
            method: "post",
            headers: {
                'Accept' : 'application/json',
            },
            body: JSON.stringify(payload)
        }
        const postResults = await fetch(gcpUrl, postOptions);
        // const jsonPost    = await postResults.json();

from this Bill FRENCH’s Script,
I just tried to script & test my own “Update Analytics” using SHEETS and Google App Script as Bill did or something closed to Bill’s way, I hope although I’m not sure !

You can find my Screen-Shots Log sorted by Date/Time here.

My Google App Script code inspiration is here
mostly from Using Google Sheets as a Web App
and Author’s GAS Code Repo is here !

Here is my own adapted GAS Code attempt:
( ! this script will never run as it is from Airtable Script Block but runs from Google App Script !)

//this is a function that fires when the webapp receives a GET request
function doGet(e) {
  return HtmlService.createHtmlOutput("get request received");
}

//this is a function that fires when the webapp receives a POST request
function doPost(e) {
  var params = JSON.stringify(e.postData.contents);
  params = JSON.parse(params);
  var myData = JSON.parse(e.postData.contents);
  var testUserid = myData.userid;
  var testUsername = myData.username;
  var testUseremail = myData.useremail;
  //
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = Math.max(sheet.getLastRow(),1);
  sheet.insertRowAfter(lastRow);
  var timestamp = new Date();
  sheet.getRange(lastRow + 1, 1).setValue(timestamp);
  sheet.getRange(lastRow + 1, 2).setValue(testUserid);
  sheet.getRange(lastRow + 1, 3).setValue(testUsername);
  sheet.getRange(lastRow + 1, 4).setValue(testUseremail);
  sheet.getRange(lastRow + 1, 5).setValue(params);
  SpreadsheetApp.flush();
  return HtmlService.createHtmlOutput("post request received");
}

Anyone who would try it only have to PUBLISH it as a WEB App as explained here , mostly from Using Google Sheets as a Web App, to get WebHook URL.

My Airtable Script-Block Script lightly adapted from Bill’s one is here:

//
    //
    //
    // update analytics
    // ACTION_02 : MyActionsRepoURL
    // inspired by : Field Tweaker - Script Block for In-place Transformations
    //               https://community.airtable.com/t/field-tweaker-script-block-for-in-place-transformations/27532/8
    // author : https://community.airtable.com/u/Bill.French
    //
    //
    
    // establish the cgp web service endpoint
    //
    // ! Anyone who would try it only have to PUBLISH it as a WEB App as explained here:
    // https://blog.runscope.com/posts/tutorial-capturing-webhooks-with-google-sheets
    // mostly from *Using Google Sheets as a Web App*, to get WebHook URL.
    //
    let gcpUrl = "https://script.google.com/macros/s/MyGoogleWEBappURL/exec";

    let payload = {
      "userid" : session.currentUser.id,
      "username" : session.currentUser.name,
      "useremail" : session.currentUser.email
    }
    
    let postOptions = {
      method: "post",
      headers: {
        'Accept' : 'application/json',
      },
      body: JSON.stringify(payload)
    }
    const postResults = await fetch(gcpUrl, postOptions);
    // const jsonPost = await postResults.json();
    //
    //
//

I got it running and POSTING to my Google SHEET
but I got 2 ISSUES:

ISSUE01:

Just Another CORS Case:

I tried to get rid of CORS by use of recent Airtable Automation Beta
but it threw an error on
session.currentUser.id , session.currentUser.name , session.currentUser.email because session. is not invocable from Automation Script TEST.

ISSUE02:

All the expected payload didn’t appear in SHEET:

“useremail” : session.currentUser.email from

let payload = {
“userid” : session.currentUser.id,
“username” : session.currentUser.name,
“useremail” : session.currentUser.email
}

is missing in SHEET after each POST.

I first thought that was result of CORS ISSUE01 but it isn’t:
When I replaced “useremail” : session.currentUser.email
by “useremail” : ‘MyOwnMailAdress’ in an AUTOMATION Script Attempt , SHEET’s POST remained the same as screenshot.

Thank you to pay attention to my little report and I hope Community could help.

oLπ

Bill_French · ‎Aug 15, 2020

It’s important to realize that Airtable’s version of javascript is vastly unlike Google’s version; these script environments are mostly incompatible and the code is unsharable between the two platforms.

What browser are you running Airtable within?

I ran your exact code - only changed the web service endpoint - worked as expected.

This pretty much proves that your CORS issue is not with Airtable or the Script Block itself, and more than likely related to your environment, possibly your browser. I recommend you read this to get a full understanding of the issues.

Even with that comprehensive explanation, it is not enough for me to truly understand the right approach. This is evidenced by the fact that I do not have a CORS Allow Anywhere plugin installed nor am I using a proxy server to establish the allow origin headers for pre-flight requests. Yet - I have no trouble running your code on Chrome. Perhaps I have a lax security setting that is making this work well for me - dunno.

But, there one thing I would try without changing any other code - remove this line; comment it out and redeploy the web service and test.

return HtmlService.createHtmlOutput("post request received");

If that doesn’t eliminate the CORS issue, try this:

Instead of returning an HTML output, change it to a plain text response with a [stringified] JSON object like this:

var json = {
  "field" : "myWebService Post",
  "value" : "success"
}
var jsonOutput = ContentService.createTextOutput(JSON.stringify(json));
jsonOutput.setMimeType(ContentService.MimeType.TEXT);
return jsonOutput

As to the server-side issue of data not being written…

This is a problem. Perhaps not “the” problem, but problematic:

var params = JSON.stringify(e.postData.contents);
params = JSON.parse(params);

There’s no reason to do this at all because your Airtable code is not passing any parameters on the URL; delete this code. You need only retrieve the postData content and parse it; it arrives stringified as evidenced by your own call in Airtable. This is what you should do at the very top of the doPost() method:

//
// parse the contents payload
//
try {
  var oContents = JSON.parse(e.postData.contents);
} catch (e) {
  var oContents = {};
}

If oContents is null, there’s nothing to process; pull the ripcord and terminate the process with prejudice. Otherwise, use the values in oContents to do something.

Wild guess - I can only suggest that possibly there is a setting in your browser that is constraining it from accessing session information, specifically user identity.

In another aspects, consider replacing all of this code:

var testUserid = myData.userid;
var testUsername = myData.username;
var testUseremail = myData.useremail;
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = Math.max(sheet.getLastRow(),1);
sheet.insertRowAfter(lastRow);
var timestamp = new Date();
sheet.getRange(lastRow + 1, 1).setValue(timestamp);
sheet.getRange(lastRow + 1, 2).setValue(testUserid);
sheet.getRange(lastRow + 1, 3).setValue(testUsername);
sheet.getRange(lastRow + 1, 4).setValue(testUseremail);
sheet.getRange(lastRow + 1, 5).setValue(params);

… with something far less complex like these three lines:

var sheet = SpreadsheetApp.getActiveSheet();
var aRowData = [[myData.userid, myData.username, myData.useremail]];
sheet.getRange("A" + (sheet.getLastRow() + 1) + ":C" + (sheet.getLastRow() + 1)).setValues(aRowData);

Aside from executing ~7 times faster, there are only three lines to debug. :winking_face:

Olpy_Acaflo · ‎Aug 15, 2020

Yes ! I add this to prevent someone who doesn’t follow you about Airtable Script and Google App Script would try to copy this to any Airtable Script API …-BLOCK or …AUTOMATION :slightly_smiling_face:

This one, on MAC :

Thank you @Bill.French,

olπ

Olpy_Acaflo · ‎Aug 16, 2020

Hi @Bill.French,

Firstly a big big thank you to have read me and examined the situation in details before proposed me some attempts to explore : what I did since yesterday !

I have read carefully this MEDIUM article about CORS that you advised:
It was very instructive, and I have taken the most interesting insight from it, it seems to me for the moment, after I understand better what it’s all about:
Take up their PROXY proposal and dig into it later in my ToDo.
Why?

To avoid dependence on a Third-Part Solution;
not to pay them rent for isolated low traffic experiments (for the moment);
learn by doing;
But I’ll reopen this topic soon in my Lab because I don’t want to be stuck by CORS at all API-Calls when I will choose (finally) and develop the FrontEnd that suits me for airtable.

All this is expressed in a rather simplistic way because I still place myself only from the point of view of the Experimenter that I am but these problems of CORS could become blocking barriers in case of access to my frontend by even a few beta-testers of my choice! And I stop there because this takes us a little bit away from the subject I had discussed which was a particular case but CORS seems to be a general problem:
Requests to one or more APIs are executed from the client side and CORS then blocks this execution, always said in a much more simplistic way than the MEDIUM article you advised…
So I feel concerned on a much more general level than the particular case I have just outlined.

Bill.French:

Instead of returning an HTML output, change it to a plain text response with a [stringified] JSON object like this:
var json = {
  "field" : "myWebService Post",
  "value" : "success"
}
var jsonOutput = ContentService.createTextOutput(JSON.stringify(json));
jsonOutput.setMimeType(ContentService.MimeType.TEXT);
return jsonOutput

Bill.French:

As to the server-side issue of data not being written…

This is a problem. Perhaps not “the” problem, but problematic:
var params = JSON.stringify(e.postData.contents);
params = JSON.parse(params);
There’s no reason to do this at all because your Airtable code is not passing any parameters on the URL; delete this code. You need only retrieve the postData content and parse it; it arrives stringified as evidenced by your own call in Airtable. This is what you should do at the very top of the doPost() method:
//
// parse the contents payload
//
try {
  var oContents = JSON.parse(e.postData.contents);
} catch (e) {
  var oContents = {};
}
If oContents is null, there’s nothing to process; pull the ripcord and terminate the process with prejudice. Otherwise, use the values in oContents to do something.

Bill.French:

In another aspects, consider replacing all of this code:

var testUserid = myData.userid;
var testUsername = myData.username;
var testUseremail = myData.useremail;
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = Math.max(sheet.getLastRow(),1);
sheet.insertRowAfter(lastRow);
var timestamp = new Date();
sheet.getRange(lastRow + 1, 1).setValue(timestamp);
sheet.getRange(lastRow + 1, 2).setValue(testUserid);
sheet.getRange(lastRow + 1, 3).setValue(testUsername);
sheet.getRange(lastRow + 1, 4).setValue(testUseremail);
sheet.getRange(lastRow + 1, 5).setValue(params);

… with something far less complex like these three lines:

var sheet = SpreadsheetApp.getActiveSheet();
var aRowData = [[myData.userid, myData.username, myData.useremail]];
sheet.getRange("A" + (sheet.getLastRow() + 1) + ":C" + (sheet.getLastRow() + 1)).setValues(aRowData);

Aside from executing ~7 times faster, there are only three lines to debug.

I tried step by step all the advice you gave about this code which was not written by me but adapted from a source I quoted in my first post:

Article;
REPO;

and I didn’t get the CORS ERROR message disappearing in airtable-Script-Block even though on Google server side, the Payload was still listed in the Google SHEET.

I will conclude for this present moment while staying open and listening by adding that by recreating a Sheet, the same today’s edited GAS Code and a Webhook URL, I have not been able to reproduce the problem of incomplete writing in the SHEET,

whereas yesterday’s SHEET continued to show the problem.

Thank you very much for your time and tests and inspirations,

olπ