I am working on a project in Apps Script. I have a set to track which ID numbers have submitted a form already, in order to only consider their first submissions. However, in between form submissions, it seems that my variables are "resetting".
To clarify what I mean, consider set s. In one form submission, the value 1111 is entered into it. In another form submission, the value 1234 is entered into it. But after the second submission, logging the contents of s simply shows 1234, without the original 1111. Is there any way I can fix this? My code is below.
function onFormSubmit(event) {
var subs = new Set();
var dict = new Object();
var teamID = event.namedValues.ID[0];
if(!subs.has(teamID))
{
subs.add(teamID);
dict[teamID] = 0;
}
}
From your question and your showing script, if your function onFormSubmit is automatically run when the Google Form is submitted, the variables in the function are declared every run of the script, when Google Form is submitted. So, when onFormSubmit is run 2nd time, the values of 1st run are cleared.
From To clarify what I mean, consider set s. In one form submission, the value 1111 is entered into it. In another form submission, the value 1234 is entered into it. But after the second submission, logging the contents of s simply shows 1234, without the original 1111. Is there any way I can fix this? and your showing script, I guess that the reason for your current issue is due to the above situation.
If you want to compare the current submitted value var teamID = event.namedValues.ID[0] with the previously submitted values, it is required to retrieve the previously submitted values, while the current submitted value can be retrieved from the event object.
When this is reflected in a sample script, how about the following modification patterns?
In this pattern, the previous values are stored in PropertiesService.
function onFormSubmit(event) {
var p = PropertiesService.getScriptProperties();
var previousValues = p.getProperty("previousValues");
var previousObj = p.getProperty("previousObj");
var subs = new Set(previousValues ? JSON.parse(previousValues) : null);
var dict = new Object(previousObj ? JSON.parse(previousObj) : null);
var teamID = event.namedValues.ID[0];
if (!subs.has(teamID)) {
subs.add(teamID);
dict[teamID] = 0;
}
console.log([...subs]); // You can confirm the current value in the log.
console.log(dict); // You can confirm the current value in the log.
p.setProperty("previousValues", JSON.stringify([...subs]));
p.setProperty("previousObj", JSON.stringify(dict));
}
onFormSubmit is installed as the form submit trigger when the Google Form is submitted, the objects of subs and dict are grown using PropertiesService. By this, you can see the previously submitted values.In this pattern, the previous values are directly retrieved from Google Form.
function onFormSubmit(event) {
var { range } = event;
var sheet = range.getSheet();
var previousValues = FormApp.openByUrl(sheet.getFormUrl()).getResponses()
.flatMap(r => r.getItemResponses()
.reduce((ar, s) => {
var res = s.getResponse();
if (s.getItem().getTitle() == "ID" && res) {
ar.push(res);
}
return ar;
}, [])
).slice(0, -1);
var previousValueObj = new Set(previousValues);
var teamID = event.namedValues.ID[0];
console.log([...previousValueObj]); // You can see previous values in the log.
console.log(teamID); // You can see the current submitted value in the log.
}
or
function onFormSubmit(event) {
var { range } = event;
var sheet = range.getSheet();
var previousValues = FormApp.openByUrl(sheet.getFormUrl()).getResponses()
.flatMap(r => r.getItemResponses()
.reduce((ar, s) => {
var res = s.getResponse();
if (s.getItem().getTitle() == "ID" && res) {
ar.push(res);
}
return ar;
}, [])
);
var teamID = previousValues.pop();
var previousValueObj = new Set(previousValues);
console.log([...previousValueObj]); // You can see previous values in the log.
console.log(teamID); // You can see the current submitted value in the log.
}
onFormSubmit is installed as the form submit trigger when the Google Form is submitted, previousValueObj is an object of Set including the previous values of "ID". And, teamID is a value of the current submitted values.