I am 16, so please understand this is probably not gonna be the greatest code you’ve seen. Anyways, I am writing a system for Google Sheets where users in my organization can update table data, but the data isn’t pushed to the main spreadsheet until it is approved by an administrator. However, when trying to send data from my backend to my spreadsheet, I receive a CORS error. I am using a Web App deployment, but I may have misconfigured something. The code is hosted on a webserver.
I have tried this code
function doPost(e) {
return ContentService.createTextOutput(JSON.stringify({status: "success", "data": "my-data"})).setMimeType(ContentService.MimeType.JSON);
}
which I found at this StackOverflow post. That worked for a moment, but now it is not working anymore. What are the next steps?
Google App Script:
function doPost(e) {
return ContentService.createTextOutput(JSON.stringify({status: "success", "data": "my-data"})).setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
// Set CORS headers
var headers = {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Methods': 'POST, GET, OPTIONS',
'Access-Control-Allow-Headers': 'Content-Type'
};
var response = {};
try {
// Parse the POST data
var data = JSON.parse(e.postData.contents);
if (data.buttonPressed === 'true') {
// Process your data here
response.status = 'Success';
response.message = 'Data push request received';
} else {
response.status = 'Error';
response.message = 'Invalid request data';
}
} catch (error) {
response.status = 'Error';
response.message = 'Failed to process request: ' + error.message;
}
// Return the response with CORS headers
return ContentService.createTextOutput(JSON.stringify(response))
.setMimeType(ContentService.MimeType.JSON)
.setHeaders(headers);
}
function doOptions() {
// Handle preflight OPTIONS requests
return ContentService.createTextOutput('')
.setMimeType(ContentService.MimeType.TEXT)
.setHeaders({
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Methods': 'POST, GET, OPTIONS',
'Access-Control-Allow-Headers': 'Content-Type'
});
}
function pushChanges() {
// Your existing pushChanges function here
const admins = ["[email protected]"];
const userEmail = Session.getEffectiveUser().getEmail();
if (admins.includes(userEmail)) {
Logger.log('User is an admin. No email sent.');
return;
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
let allData = {};
sheets.forEach(sheet => {
const data = sheet.getDataRange().getValues();
const sheetName = sheet.getName();
allData[sheetName] = data.map(row =>
row.map(cell => encodeURIComponent(cell.toString().replace(/"/g, '""'))).join(',')).join('%0A');
});
const formattedData = Object.keys(allData)
.map(sheet => `sheet=${encodeURIComponent(sheet)}&data=${allData[sheet]}`)
.join('&');
const baseUrl = 'https://landenbarker.com/PushData?';
const fullUrl = baseUrl + formattedData + `&editormail=${encodeURIComponent(userEmail)}`;
const subject = 'Spreadsheet Data Updated';
const body = 'The data in the spreadsheet has been updated. You can view the data at the following link:nn' + fullUrl;
admins.forEach(admin => {
MailApp.sendEmail(admin, subject, body);
});
Logger.log('Email sent to admins with data link.');
}
HTML Backend:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CSV Data and Push Changes</title>
<style>
table {
width: 100%;
border-collapse: collapse;
margin: 20px 0;
}
table, th, td {
border: 1px solid #ddd;
}
th, td {
padding: 8px;
text-align: left;
}
th {
background-color: #f4f4f4;
}
tr:nth-child(even) {
background-color: #f9f9f9;
}
#buttonContainer {
margin: 20px 0;
text-align: center;
}
.button {
border: none;
padding: 10px 20px;
margin: 0 10px;
font-size: 16px;
cursor: pointer;
color: white;
border-radius: 5px;
display: inline-flex;
align-items: center;
}
.button.checkmark {
background-color: #4CAF50; /* Green */
}
.button.xmark {
background-color: #f44336; /* Red */
}
.button img {
margin-right: 8px;
}
</style>
</head>
<body>
<h1>CSV Data and Push Changes</h1>
<div id="contactInfo">Loading...</div>
<div id="csvTable">Loading...</div>
<div id="buttonContainer">
<button class="button checkmark" onclick="pushData()">
<img src="https://img.icons8.com/material-outlined/24/checked.png" alt="Checkmark Icon"> Push Data
</button>
<button class="button xmark" onclick="discardData()">
<img src="https://img.icons8.com/material-outlined/24/delete-forever.png" alt="X Icon"> Discard Data
</button>
</div>
<script>
function getDataFromCurrentUrl() {
const url = window.location.href;
const urlParams = new URLSearchParams(new URL(url).search);
const dataParam = urlParams.get('data');
const editorMail = urlParams.get('editormail');
const contactInfoDiv = document.getElementById('contactInfo');
if (editorMail) {
contactInfoDiv.innerHTML = `<p>Email: ${decodeURIComponent(editorMail)}</p>`;
} else {
contactInfoDiv.innerHTML = 'No email parameter found in URL.';
}
const csvTableDiv = document.getElementById('csvTable');
if (dataParam) {
try {
const decodedData = decodeURIComponent(dataParam);
const csvLines = decodedData.split('n');
const headers = csvLines[0].split(',');
const rows = csvLines.slice(1).map(line => line.split(','));
let tableHtml = '<table><thead><tr>';
headers.forEach(header => {
tableHtml += `<th>${header}</th>`;
});
tableHtml += '</tr></thead><tbody>';
rows.forEach(row => {
tableHtml += '<tr>';
row.forEach(cell => {
tableHtml += `<td>${cell}</td>`;
});
tableHtml += '</tr>';
});
tableHtml += '</tbody></table>';
csvTableDiv.innerHTML = tableHtml;
} catch (e) {
csvTableDiv.innerHTML = 'Error processing CSV: ' + e.message;
}
} else {
csvTableDiv.innerHTML = 'No data parameter found in URL.';
}
}
function pushData() {
const scriptUrl = 'https://script.google.com/a/macros/student.nisdtx.org/s/AKfycbwcXp9ovTej4Yn4_IqdZTrvBsBFcqVT-bEHoZbNmV1pU3CAk0CNjucxDyLwuaYyLlrX6A/exec';
fetch(scriptUrl, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Accept': 'application/json'
},
body: JSON.stringify({ buttonPressed: 'true' })
})
.then(response => response.json())
.then(result => {
console.log('Success:', result);
alert('Data push request sent successfully.');
})
.catch(error => {
console.error('Error:', error);
alert('Failed to send data push request. Check the console for details.');
});
}
function discardData() {
alert('Discard Data button clicked');
// Add your discard data logic here
}
// Run the function to display data on page load
getDataFromCurrentUrl();
</script>
</body>
</html>
I have been looking for a StackOverflow solution for quite a bit and have yet to find one.