{"id":4311,"date":"2026-02-24T08:50:51","date_gmt":"2026-02-24T13:50:51","guid":{"rendered":"https:\/\/cs4760.csl.mtu.edu\/2026\/?page_id=4311"},"modified":"2026-02-24T09:03:19","modified_gmt":"2026-02-24T14:03:19","slug":"google-app-script","status":"publish","type":"page","link":"https:\/\/cs4760.csl.mtu.edu\/2026\/lectures\/google-app-script\/","title":{"rendered":"Google App Script"},"content":{"rendered":"\n

By Duncan McBride<\/p>\n\n\n\n

Introduction<\/h2>\n\n\n\n

Google App Script is a service from Google that allows you to write custom code which interacts with a Google file. This tutorial will mostly cover using App Script with a Google Sheet (henceforth sheet or spreadsheet). <\/p>\n\n\n\n

The main goal for our use case is to make spreadsheet submissions accessible to anyone without making the spreadsheet itself public. How? By setting up an endpoint our main project can use to POST new form data. The data is sent to the endpoint via http and our code adds it to the spreadsheet just how we want it. This way we can record user input with reduced risks of griefing and malicious use. With App Script, you don\u2019t have to worry about hosting; Google takes care of that.<\/p>\n\n\n\n

Set Up<\/h2>\n\n\n\n

To get started, make a new Google Sheet. In each column of the top row of this spreadsheet, write the name of a field you\u2019d like to collect. For example, we could put (Name, Email, Phone Number) in A1, B1, and C1 respectively. Next we\u2019ll connect this spreadsheet to App Scripts. In the top menu bar, select Extensions -> App Script. This will open a new App Scripts project.<\/p>\n\n\n\n

Get and Post Endpoints<\/h2>\n\n\n\n

For our use case, we need two functions, one for sending data and the other for receiving it. Here\u2019s the javascript for a basic example. <\/p>\n\n\n\n

\/\/ The default spreadsheet name is Sheet1, but if you have a different name, update it here.\nconst SHEET_NAME = \"Sheet1\";\n\n\n\/**\n * POST requests - This adds a new row with the new data.\n *\/\nfunction doPost(e) {\n  try {\n    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);\n   \n    \/\/ 'e.parameter' contains the fields from the FormData\n    \/\/ If you have more\/different fields, change this array.\n    const newRow = [\n      e.parameter.Name,         \/\/ Column A\n      e.parameter.Email,        \/\/ Column B\n      e.parameter['Phone Number'] \/\/ Column C    ];\n   \n\/\/ Take the new data and add it to the sheet\n    sheet.appendRow(newRow);\n   \n    \/\/ Return a success JSON\n    return ContentService.createOutput(JSON.stringify({\n        result: 'success',\n        row: sheet.getLastRow()\n    }))\n    .addHeader(\"Access-Control-Allow-Origin\", \"*\")\n    .setMimeType(ContentService.MimeType.JSON);\n\n\n  } catch (err) {\n    \/\/ Return an error JSON\n    return ContentService.createOutput(JSON.stringify({\n        result: 'error',\n        message: err.message\n    }))\n    .addHeader(\"Access-Control-Allow-Origin\", \"*\")\n    .setMimeType(ContentService.MimeType.JSON);\n  }\n}\n\n\n\/\/ GET handler, for sending data without giving sheet permissions.\nfunction doGet(e) {\n  try {\n\/\/ Get the target sheet\n    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);\n    const allData = sheet.getDataRange().getValues();\n   \n    \/\/ Filter for the first 3 columns\n    const filteredData = allData.map(function(row) {\n      return row.slice(0, 4); \/\/ Gets columns 1-3\n    });\n   \n    \/\/ Return the data as JSON\n    return ContentService.createTextOutput(JSON.stringify(filteredData))\n      .setMimeType(ContentService.MimeType.JSON);\n     \n  } catch (err) {\n    return ContentService.createTextOutput(JSON.stringify({ error: err.message }))\n      .setMimeType(ContentService.MimeType.JSON);\n  }\n}\n<\/pre>\n\n\n\n
You should read through this code and understand what it is doing. The most important thing to check is that you are grabbing the correct number of columns for your use case. For a real life application, here\u2019s how we implemented the App Script for Croak Counter: https:\/\/github.com\/2025-UI-SP\/SP-Croak-Counter\/blob\/master\/backend.gs<\/a> <\/p>\n\n\n\n
Once you have the code set up, select the Deploy button in the top right corner of the window. Set a version description, set yourself as the executor, and make sure the app can be accessed by anyone. You\u2019re all set on Google\u2019s end! Once deployed, you\u2019ll get a Web App URL like this (https:\/\/script.google.com\/macros\/s\/AKfycbwNnbH2HoyJhjJszMdEADa2ePwjgDOWz57w0KQnh7j82QH6PMZbZAuaFvE8MM31u4HM\/exec<\/a>). This is your endpoint. Anytime you want external code to access your script, it will do so via a POST or GET request to this address. Speaking of which\u2026<\/p>\n\n\n\n
Client Side Call<\/h2>\n\n\n\n
How exactly do we use this URL? In our App Script, we set up two functions that deal with either a POST or GET request. Based on this, we can make a GET or POST request in our app. Here\u2019s an example:<\/p>\n\n\n\n
 <script>\n          \/\/ Hook URL - This is where to put the Web App URL you got from your App Script deployment.\n        const SCRIPT_URL = \"https:\/\/script.google.com\/macros\/s\/AKfycby--HVEiiuzRa2mJFS4EWDokGN_Z9CQsjCyueQ070GKpPHmr0h4gJFX8Cu5Jgi21BZyOw\/exec\";\n       \n       \/\/ Basic form handeling\n        const form = document.getElementById('myForm');\n        const statusMessage = document.getElementById('statusMessage');\n\n\n        form.addEventListener('submit', function(e) {\n            e.preventDefault();\n            statusMessage.innerHTML = \"Sending...\";\n           \n            const formData = new FormData(form);\n\n\n            fetch(SCRIPT_URL, {\n                method: 'POST',\n                body: formData\n            })\n            .then(response => response.json())\n            .then(data => {\n                if (data.result === 'success') {\n                    statusMessage.innerHTML = \"Success! Data added to row \" + data.row + \".\";\n                    form.reset();\n                    \/\/ Upon form submit, reload data\n                    loadData();\n                } else {\n                    statusMessage.innerHTML = \"Error: \" + data.message;\n                }\n            })\n            .catch(error => {\n                console.error('Error:', error);\n                statusMessage.innerHTML = \"Error: Could not send data. \" + error.message;\n            });\n        });\n\n\n       \n        \/\/ Run this function when the page loads\n        window.addEventListener('load', loadData);\n   \n        function loadData() {\n            document.getElementById('loader').style.display = 'block';\n            document.getElementById('data-container').innerHTML = '';\n\n\n            \/\/ Fetch data from .gs 'doGet' function\n            fetch(SCRIPT_URL)\n                .then(response => response.json())\n                .then(dataRows => {\n                    \/\/ Error catching\n                    if (dataRows.error) {\n                        showError(dataRows);\n                        return;\n                    }\n\n\n                    document.getElementById('loader').style.display = 'none';\n                    const container = document.getElementById('data-container');\n                   \n                    \/\/ Basic HTML table\n                    let table = '<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\">';\n                    dataRows.forEach((row, rowIndex) => {\n                        \/\/ Use <th> for the first row (header), <td> for the rest\n                        let cellTag = (rowIndex === 0) ? 'th' : 'td';\n                        table += '<tr>';\n                        row.forEach(cell => {\n                            table += `<${cellTag}>${cell}<\/${cellTag}>`;\n                        });\n                        table += '<\/tr>';\n                    });\n                    table += '<\/table>';\n                    container.innerHTML = table;\n                })\n                .catch(error => {\n                    console.error('Load Data Error:', error);\n                    showError({ message: \"Could not fetch data. \" + error.message });\n                });\n        }\n   \n        function showError(error) {\n            document.getElementById('loader').innerText = 'Error: ' + error.message;\n            console.error('Apps Script call failed:', error);\n        }\n    <\/script>\n<\/pre>\n\n\n\n
This bit of JavaScript can run in a website external to the App Script. By using GET and POST, it can interact with our deployed App Script code. Of course, your use case may be slightly more complicated, but the basic GET and POST system is the same. Here\u2019s how we implement it in Croak Counter:<\/p>\n\n\n\n
https:\/\/github.com\/2025-UI-SP\/SP-Croak-Counter\/blob\/master\/src\/pages\/Observations.jsx<\/a><\/p>\n\n\n\n
Look (or ctl-f) for \u201cconst doUpload\u201d, which is our upload function. At the time of writing, it\u2019s around line 248.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
App Scripts is fairly powerful. There\u2019s quite a bit more that you can do with the service (documentation below). Do be aware that these endpoints can still be abused; it\u2019s worth checking the input data every once in a while.<\/p>\n\n\n\n
Additional Reading<\/h2>\n\n\n\n