I’m currently developing a script to manage a company resources using a Google Spreadsheet, where people would request a resource and inform a due date to it, when that date comes a email is sent remembering the person to give it back. The script works fine when I’m using my account, the account in which I created the spreadsheet and wrote the script. However, when I share the script with another account (with editor authorization), every time the person runs the script, and the google.script.run is called the scripts just shows “paused” until the time out.
I’ve tried different deployments settings, and the same happens. I’ve also tested that the issue seems to reproduce only when the google.script.run is called through a HTML form I made on a sidebar menu.
Here is the current Code.gs:
/**
* @OnlyCurrentDoc
*/
// function getToken() {
// let token = ScriptApp.getOAuthToken()
// return token;
// }
// Displays the menu and the sidebar once the Spreadsheet is loaded
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('ESIM Menu')
.addItem('Request ESIM', 'showRequestEsimSidebarForm')
.addItem('Release ESIM', 'showReleaseEsimSidebarForm')
.addItem('Test', 'test')
.addToUi();
// updateLineCells()
}
function test() {
displayAlertMessage("Teste", "Teste")
}
function onEdit(e) {
updateLineCells()
}
// Creates and shows the RequestEsimSideBarForm
function showRequestEsimSidebarForm() {
var userForm = HtmlService.createTemplateFromFile("request-esim-form").evaluate().setTitle("Esim Request");
SpreadsheetApp.getUi().showSidebar(userForm)
}
// Creates and shows the ReleaseEsimSideBarForm
function showReleaseEsimSidebarForm() {
var userForm = HtmlService.createTemplateFromFile("release-esim-form").evaluate().setTitle("Esim Release");
SpreadsheetApp.getUi().showSidebar(userForm)
}
function updateLineCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let data = sheet.getDataRange().getValues();
let headers = data[0];
let lineIndex = headers.indexOf("LINE");
let availabilityIndex = headers.indexOf("IS AVAILABLE");
for (let i = 1; i < data.length; i++) {
let isAvailable = data[i][availabilityIndex];
let lineCell = sheet.getRange(i+1, lineIndex+1);
if (isAvailable === true || isAvailable === "TRUE") {
lineCell.setBackground("#00ff00");
} else {
lineCell.setBackground("#ff0000");
}
}
}
function displayAlertMessage(title, message) {
let ui = SpreadsheetApp.getUi();
ui.alert(title, message, ui.ButtonSet.OK);
}
function displayApprover(title="Teste", message="TEste") {
let ui = SpreadsheetApp.getUi();
let response = ui.alert(title, message, ui.ButtonSet.YES_NO);
if (response === ui.Button.YES) {
return true;
} else {
return false
}
}
function findMatch(sheet, value) {
if (value || value.trim() != "") {
let texFinder = sheet.createTextFinder(value);
texFinder.matchEntireCell(true);
let match = texFinder.findNext();
if (!match) {
return null;
}
return match;
} else {
return null;
}
}
function checkCellAvailability(sheet, cell) {
let headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
let availabilityIndex = headers.indexOf("IS AVAILABLE");
let availabilityCell = sheet.getRange(cell.getRow(), availabilityIndex + 1);
let isAvailable = availabilityCell.getValue()
if (isAvailable == true || isAvailable == "TRUE") {
return true
} else {
return false
}
}
function saveRequest({name, email, finalDate, esimLine}, sheet) {
var data = sheet.getDataRange().getValues();
var headers = data[0];
let esimLineIndex = headers.indexOf("LINE");
let currentResponsibleIndex = headers.indexOf("CURRENT RESPONSIBLE");
let returnDateIndex = headers.indexOf("RETURN DATE");
let availabilityIndex = headers.indexOf("IS AVAILABLE");
for (let i = 1; i < data.length; i++) {
if (data[i][esimLineIndex] == esimLine) {
sheet.getRange(i+1, currentResponsibleIndex+1).setValue(name);
sheet.getRange(i+1, returnDateIndex+1).setValue(finalDate);
sheet.getRange(i+1, availabilityIndex+1).setValue("FALSE");
displayAlertMessage("Request Saved", `${name} is now responsible for the ESIM line: ${esimLine}.`)
break;
}
}
updateLineCells()
}
// Handles the event when the Submit button is pressed
// THIS IS THE FUNCTION THAT GOES TO "PAUSED" STATUS WHEN ANOTHER USER WHO IS NOT THE CREATOR RUNS THE SCRIPT
function handleRequestEvent(data) {
// Surprisingly this log is displayed on the logs, but any log after this line is not displayed
Logger.log("teste")
let isApproved = displayApprover("Validation", `Do you want to move the following ESIM line: ${data["esimLine"]} to ${data["name"]}? Return date: ${data["finalDate"]}.`)
if (!isApproved) {
return;
}
try {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let match = findMatch(sheet, data["esimLine"]);
if (match != null) {
let isAvailable = checkCellAvailability(sheet, match)
if(isAvailable) {
saveRequest(data, sheet);
} else {
displayAlertMessage("Esim not available", "The Esim you informed is already in use, please choose another one.")
}
} else {
displayAlertMessage("Esim not found", "The Esim you informed was not found on the table, please check again.")
}
} catch(error) {
// displayAlertMessage("Something went wrong", "Sorry, something went wrong, if the error persistis, please contact support.")
displayAlertMessage("Erro", `${error.stack}`)
}
}
// Not implemented yet
function handleReleaseEvent(data) {
try {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
displayAlertMessage("Teste", "Rello")
} catch(error) {
displayAlertMessage("Erro", `${error.stack}`)
}
}
// Sends an email to every email attached to a client
function sendReminderEmail() {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let data = sheet.getDataRange().getValues();
let headers = data[0];
let returnDateIndex = headers.indexOf("RETURN DATE");
let isAvailableIndex = headers.indexOf("IS AVAILABLE");
let currentResponsibleIndex = headers.indexOf("CURRENT RESPONSIBLE");
let esimLineIndex = headers.indexOf("LINE");
let today = new Date();
today.setHours(0, 0, 0, 0)
// Email won't be sent on Weekends
if (today.getDay() === 6 || today.getDay() === 0) {
return;
}
let reminderMap = {};
for (let i = 1; i < data.length; i++) {
let returnDate = new Date(data[i][returnDateIndex]);
let isAvailable = data[i][isAvailableIndex];
let responsible = data[i][currentResponsibleIndex];
let esimLine = data[i][esimLineIndex];
if (isAvailable == true || isAvailable == "TRUE") {
continue;
}
if (returnDate <= today && (isAvailable === false || isAvailable === "FALSE")) {
if (responsible) {
if (!reminderMap[responsible]) {
reminderMap[responsible] = [];
}
reminderMap[responsible].push(esimLine);
}
}
}
for (let responsible in reminderMap) {
let esimList = reminderMap[responsible].join(', ');
let emailBody = `
Hello ${responsible},<br><br>
This is a reminder to free the following ESIM lines as their return dates have passed or are due today:<br>
<strong>${esimList}</strong><br><br>
Please make sure to release them as soon as possible.<br><br>
Best regards,<br>
ESIM Management Team
`;
MailApp.sendEmail({
to: "[email protected]",
subject: "ESIM Return Reminder",
htmlBody: emailBody
});
}
};
Here as follows is the HTML form that is displayed on the sidebar and calls the google.script.run.handleRequestEvent():
<!DOCTYPE html>
<html lang="en">
<head>
<base target="_top">
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
</head>
<body>
<div class="container">
<form id="EsimRequestForm">
<div class="mb-3">
<label for="SelectName" class="form-label">Name</label>
<select class="form-select" id="SelectName" aria-label="name select input">
<option selected>Open this select menu</option>
<option value="Natalia Lima">Natalia Lima</option>
<option value="Jonas Felix">Jonas Felix</option>
<option value="Flavia Andrade">Flavia Andrade</option>
</select>
</div>
<div class="mb-3">
<label for="InputEmail" class="form-label">Email address</label>
<input type="email" class="form-control" id="InputEmail" aria-describedby="emailHelp" required>
</div>
<div class="mb-3">
<label for="InputEsimLine" class="form-label">Esim Line</label>
<input type="tel" class="form-control" id="InputEsimLine" aria-describedby="esimLineHelp" required>
</div>
<div class="mb-3">
<label for="InputDate" class="form-label">Will use it until</label>
<input type="date" class="form-control" id="InputDate" aria-describedby="dateHelp" required>
</div>
<button type="submit" class="btn btn-primary" id="submitButton">Submit</button>
</form>
</div>
</body>
<script>
var userName = document.querySelector("#SelectName")
var userEmail = document.querySelector("#InputEmail")
var esimLine = document.querySelector("#InputEsimLine")
var finalDate = document.querySelector("#InputDate")
var requestForm = document.querySelector("#EsimRequestForm")
requestForm.addEventListener('submit', (e)=> {
e.preventDefault();
var data = {
"name": userName.value,
"email": userEmail.value,
"finalDate": finalDate.value,
"esimLine": esimLine.value
}
google.script.run.handleRequestEvent(data)
})
</script>
</html>
From what I understand it is not a code issue, because it works properly when I’m using the “owner” account, it seems to be a authorization issue of some sort, but the sidebar is displayed to the other users, and they can call the Test dialog through the Test option on the menu.