I'm stuck I have done something similar before and it worked but this time round I keep getting a "Sorry, unable to open the file at present." error message.
I want to send an email to a number of participants and once they click on the button in the email my google sheet will update with a simple "Yes", my code is below for the sake completeness I have added everything apologies if it's all a bit much.
GAS code:
function sendChallengeEmail() {
var url = 'https://script.google.com/macros/s/AKfycbzqVFeYC0O1PVHA051UxvrmdJgDJ9gyjhw5X7Lks6j5LGhmRpbCaRLVS38I--ySTLAS/exec'
const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1NbDbt3IzcXBcZeIa2_XERql4AxnUOQyZDvNRTiaNVXg/edit?pli=1#gid=1386761827");
const challenges = ss.getSheetByName("Challenges");
const responses = ss.getSheetByName("Responses");
var challengesLen = challenges.getLastRow();
var challenge = '';
var challengeCol;
var date = Utilities.formatDate(new Date, "GMT", "dd/MM/yyyy").toString();
for (var i = 1; i <= challengesLen; i++) {
if (challenges.getRange(i,1).getValue() == date) {
challenge = challenges.getRange(i,2).getValue();
}
}
for (var j = 1; j <= responses.getLastColumn(); j++) {
if (responses.getRange(1,j).getValue() == challenge) {
challengeCol = j;
}
}
var day = challenge.split(" ")[1];
Logger.log(challenge);
Logger.log(challengeCol);
var data = responses.getRange(2,1,responses.getLastRow()-1,13).getValues();
data.forEach(function(row, i) {
var participant = row[1];
var participantEmail = row[2]
var emailSent = row[challengeCol - 2];
Logger.log(row);
Logger.log(emailSent);
Logger.log(challengeCol-1);
if (emailSent == '') {
Logger.log(emailSent);
var emailSentCol = challengeCol - 1;
var workingRow = i + 2;
var submit = url + "?submit=Yes" + "&challenge=" + challenge + "&r=" + workingRow + "&c=" + challengeCol;
var templ = HtmlService.createTemplateFromFile('emailBody');
templ.participant = participant;
templ.day = day;
templ.challenge = challenge;
templ.submit = submit;
var message = templ.evaluate().getContent();
MailApp.sendEmail({
to: participantEmail,
subject: "test",
htmlBody: message
});
var d = new Date();
responses.getRange(workingRow, emailSentCol).setValue(d);
};
});
}
function doGet(e) {
var answer = e.parameter.submit;
var workingRow = e.parameter.r;
var workingCol = e.parameter.c;
responses.getRange(workingRow, workingCol).setValue(answer);
var app = HtmlService.createHtmlOutput("Thank you!")
return app
}
HTML code:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
.button {
border: blue;
background-color: blue;
color: white;
padding: 15px 32px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 16px;
margin: 4px 2px;
cursor: pointer;
}
</style>
</head>
<body>
<p>Hello <?= participant ?> </p>
<p>You are now on Day <?= day ?> of the challenge!</p>
<p>Today's challenge is:</p>
<?= challenge ?>
<p>Don't forget to submit once you have completed your challenge!</p>
<a href = <?= submit ?> button id = "btn" onclick ="getElementById('btn').innerHTML='CONGRATULATIONS'" class="button">Challenge completed!</a>
<p>And finally how about sharing an image of your challenge :)</p>
<form>
<label for = '"url " + <?= day ?>'>Enter image url here: </label>
<input type = "url <?= day ?>" class = textarea id = "url" name = "data[url <?= day ?>]">
<button type = "submit" class="btn btn-primary btn-block">Submit</button>
</form>
</body>
</html>
I am only logged into one google account I know this has been suggested in other posts.