CODEWITHSUNDEEP
javascriptformsgoogle-apps-scriptgoogle-sheets
iamconfusion
iamconfusion

Reputation: 47

Error submitting form to Google Sheets which causes a CORS error

I currently have a form that runs on localhost that submits data to a Google Sheet's script to post it onto a spreadsheet. Everytime I submit the form, I get this error: Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https : // script.google.com/macros/s/xxxxxxxxx/exec. (Reason: CORS header ‘Access-Control-Allow-Origin’ missing)

On Google Scripts side, I have already set up the deployment as: Execute as set to Me and Who has access set to Anyone.

Here is the javascript code utilized to submit the form.

form.addEventListener('submit', (e) => {
    e.preventDefault();
    console.log("form has been submitted!");
    fetch('https://script.google.com/macros/s/xxxxxxxxx/exec', { 
        method: 'POST', 
        mode: 'cors',
        body: new FormData(form)
    })
        .then(response => {
            if(response.ok) {
                console.log('Success!', response);
            } else {
                console.log(response);
                alert("Ohhh noooo!! ERROR please try again response: " + response.status);
            }
        })
        .catch(error => {
            console.log("Error!!" + error);
            console.error('Error!', error.message);
            alert('BEEP BOOP: there is an error, please try again');
        });
}

Here is the script for the Google Sheet

function intialSetup() {
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    scriptProp.setProperty('key', activeSpreadsheet.getId());
}

function doPost(e) {
    var lock = LockService.getScriptLock();
    lock.tryLock(10000);

    try {
        var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
        var sheet = doc.getSheetByName(sheetName);

        var json = JSON.stringify(e.postData);
        var obj = JSON.parse(json);
        var task = obj["task"];

        // spreadsheet tab names
        switch (task) {
            case "Tech Trimming":
                specificTab(task, obj["tray_number"], obj["name"], obj["qrCodeArea"].split("\n"));
                break;
            default:
                Logger.log("option does not exist");
        }

        return ContentService
            .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
            .setMimeType(ContentService.MimeType.JSON)
    }

    catch (e) {
        return ContentService
            .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
            .setMimeType(ContentService.MimeType.JSON)
    }

    finally {
        lock.releaseLock();
    }

}

function specificTab(task, trayNum, techName, impressions) {
    var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    var subSheetName = spreadSheet.getSheetByName(task);

    var date = getDate();
    var lastRow = subSheetName.getLastRow();

    for (var i = 0; i < impressions.length - 1; i++) {
        subSheetName.getRange(lastRow + 1, 1).setValue(date);
        subSheetName.getRange(lastRow + 1, 2).setValue(trayNum);
        subSheetName.getRange(lastRow + 1, 3).setValue(techName);
        subSheetName.getRange(lastRow + 1, 4).setValue(impressions[i]);

        lastRow++;
    }
}

On the execution tab on Google Scripts, all the executions are showing up as Failed if you submit.

On my other computer everything seemed to work just fine a couple of months ago. As in I can submit the form and then it would post to the spreadsheet with no issues. The computer I am currently using seems to be giving me these issues. I can't test this theory out because I don't have my other computer at the moment.

I have deployed this form onto vercel but it didn't seem to solve the problem.

Upvotes: 0

Views: 3331

Answers (1)

iansedano
iansedano

Reputation: 6481

Communicate entirely in plain text to avoid CORS errors

This means that whenever you send a POST request to your web app, JSON.stringify() all your data and then JSON.parse() if relevant. You may also need to set a header to "Content-Type": "text/plain".

That is, do not send a FormData object directly, ensure that all communication between the client and the web app happens in plain text, and then decode the information on the other side.

On the Apps Script side, ensure you are using:

ContentService.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }));
.setMimeType(ContentService.MimeType.TEXT) // Maybe you can omit this line.

And not:

ContentService.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }));
.setMimeType(ContentService.MimeType.JSON)

To respond from your doPost. It is probably also best to do this with doGet also.

no-cors

If the response from the POST request is not needed (though I imagine you would want it), you can set the request mode to mode: 'no-cors', but then your client will not have access to any information about the reponse.

CORS proxy

You can also set up (or use a third party service) a proxy server that will make the requests on the back-end, and allows CORS requests to your client.

Reference

Upvotes: 2

Related Questions