arraysmultidimensional-arraygoogle-apps-scriptgoogle-sheets

Most efficient way to get a column range into a 1D array


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:

  1. Get all the values in column B except the latest one
  2. Get the latest username
  3. Check if the latest username is in the list of values (do something if not).

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:

  1. How do I get the values into an array instead of the Logger?
  2. Is this the best way to do what I'm trying to do? It seems unnecessary that every time there's a submission, I grab a 2D array, convert to 1D, and compare. The column data is not going to change, it only grows by 1 every time. How will this impact run time as the data grows into the 1000s?

Solution

  • 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!