google-sheetsgoogle-apps-scriptgoogle-oauthurlfetchgoogle-forms-api

How to fetch google form response with required email in Google App Script


As the title, I got some problem when trying to automate response google forms type collect submitter data but I am not the form's owner Here is the form: Sample Form

Below is my scratch code in GAS:

function submitToGoogleFormWithEmail() {
    var formUrl = "https://docs.google.com/forms/d/e/1FAIpQLSdBdRIYCAaxp2FI4n0rVZywcz0Br7n2DOBOIcWSLHP9B67ikA/formResponse";
    var email = Session.getActiveUser().getEmail();
    var token = ScriptApp.getOAuthToken();
    Logger.log(token);

    var payload = {
        "entry.1507679348": "lucasdo",
        "entry.21751216": "dnd",
        "entry.528181198": "appscript",
        "emailAddress": email
    };

    var options = {
        'method': 'post',
        "muteHttpExceptions": true,
        'payload': payload,
        'headers': {
            'Authorization': 'Bearer ' + token,
            'Accept': 'application/x-www-form-urlencoded'
        }
    };

    var response = UrlFetchApp.fetch(formUrl, options);
    Logger.log(response.getResponseCode());
}

When I test the code, it showed error code 401 in console log of app script. Console Log Screenshot

I tried with browser console fetching, it worked if I logged in before and go to below url: https://docs.google.com/forms/d/e/1FAIpQLSdBdRIYCAaxp2FI4n0rVZywcz0Br7n2DOBOIcWSLHP9B67ikA/formResponse?entry.1507679348=lucasdo&entry.21751216=dnd&entry.528181198=appscript&emailAddress=my_email@gmail.com

Similar thread but not helpful so much: How to auto submitting in require sign-in google form from google apps script

Does anyone have the solution for this case?


Solution

  • I can't offer a definitive answer about using Google Apps Script server-side code to post a response to a Google Form owned by someone else that is set to collect the form respondent's email address automatically, but I can mention a couple of things.

    Regarding your attempts to use the web browser console and extensions, these options are not directly comparable with using Google Apps Script's FetchUrlApp.fetch, first because this method runs on Google's servers while the mentioned options run on the user's device. Second, the Google Apps Script user cannot control all the possibilities for making HTTP requests due to limitations set by Google, i.e., the user-agent can't be customized. For details, see https://developers.google.com/apps-script/reference/url-fetch.

    In addition to Google Apps Script, other cloud services, such as https://www.postman.com, can handle HTTP requests. Some of them allow the user to play with more options.