I have a Google form that collects responses in a linked spreadsheet. One of the fields in the form is Username
(column B in the sheet). I'm writing a script to notify me when a new user (unique username) makes a submission. To do this, I:
Here's what I've got so far:
function isUserNew() {
var spreadsheet = SpreadsheetApp.openById("INSERT ID HERE");
var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
var last_row_number = sheet.getLastRow()
var penultimate_row_number = (last_row_number-1)
var latest_username = sheet.getRange(last_row_number, 2).getValue()
var usernames = sheet.getRange("B2:B" + penultimate_row_number).getValues();
}
This gives me a 2D array usernames
that looks like [[UserA],[UserB],[UserC],[UserC],[UserA],[UserX]].
Then I tried if (usernames.indexOf(latest_username) == -1
but this doesn't work - it only works for a 1D array. I thought of converting the array to 1D using a loop:
for (var i = 0; i < usernames.length; i++) {
Logger.log(usernames[i][0]);
}
This makes the logger correctly log all the usernames I want, but I don't know how to put them into an array.
2 main questions:
Yep, you could flatten the array and compare with indexOf
as you're doing:
var flattened_usernames = [] ;
for (var i = 0; i < usernames.length; i++) {
flattened_usernames.push(usernames[i][0]);
}
But you're better off just doing the check inside the loop:
if (usernames[i][0] === latest_username)
By the way, I assume you know how to grab the username directly from the onFormSubmit
event rather than grabbing it from the last row of the sheet. But if not you should learn how to do that!