Google Form Key Issuer

script.js

/*
For use:
1. create a spreadsheet in google docs,
   make a sheet with column A being the key, column B being emails, column C being issue date
2. create a form in Google Forms, in the ... menu, create a script, link "Sheets" and "Gmail" services.
3. test run this with `formEmail` overridden to your own email for testing
4. grant all necessary permissions
5. in the trigger tab, set up the trigger on form submission.
*/

const spreadsheetId = "[FILL_THIS]";
const sheetName = "[FILL_THIS]";
const fromEmail = "[FILL_THIS]";
const startRow = 2; // change this
const maxKeysPerEmail = 5; // change this

function canonicalEmail(email) {
  if (typeof email !== "string" || email === "") {
    return undefined;
  }
  return email.toLocaleLowerCase();
}

function issueKey(event) {
  const formEmail = event?.response?.getRespondentEmail?.();
  if (typeof formEmail !== "string" || formEmail === "") {
    return;
  }

  console.log("Received event for respondent " + formEmail);
  const email = canonicalEmail(formEmail);
  console.log("Canonical Email " + email);

  const sheetValues = Sheets.Spreadsheets.Values.get(spreadsheetId, `'${sheetName}'!A${startRow}:C`);
  let keysIssued = 0;
  let firstAvailableKeyRow = undefined;
  let issuedKey = undefined;
  console.log(sheetValues);
  for (let i = 0; i < sheetValues.values.length; i++) {
    const row = sheetValues.values[i];
    const [key, issuedTo, issuedAt] = row;
    console.log(key, issuedTo, issuedAt);
    if (canonicalEmail(issuedTo) === email) {
      keysIssued++;
    }
    if (firstAvailableKeyRow === undefined && issuedTo === undefined && key !== undefined && key !== "") {
      firstAvailableKeyRow = i + startRow;
      issuedKey = key;
    }
  }

  const rawMessage = [
    `From: PartyLAN <${fromEmail}>`,
    `To: PartyLAN User <${email}>`,
    `Content-Type: text/plain; charset=utf-8`,
    `MIME-Version: 1.0`,
    `Subject: Steam Key Request`,
    ``,
  ];

  if (keysIssued >= maxKeysPerEmail) {
    console.log(`Max key count exeeded for ${email}`);
    rawMessage.push(`Your key request has been denied since you have already requested at least ${maxKeysPerEmail} keys.`);
  } else if (firstAvailableKeyRow === undefined) {
    console.log("No available key found");
    rawMessage.push(`We've received your key request. However, we are unable to fulfill that request since we don't have any more keys available at the moment. You can try again later.`);
  } else {
    const update = Sheets.Spreadsheets.Values.update(
      {
        "majorDimension": "ROWS",
        "values": [[email, (new Date()).toISOString()]]
      },
      spreadsheetId,
      `B${firstAvailableKeyRow}:C${firstAvailableKeyRow}`,
      { valueInputOption: "USER_ENTERED" }
    );
    console.log("Updated Spreadsheet", update);
    console.log(`Key ${issuedKey} issued to ${email}`);
    rawMessage.push(`Your Steam key is ${issuedKey}. You can redeem it at https://store.steampowered.com/account/registerkey`);
  }

  Gmail.Users.Messages.send({
    raw: Utilities.base64EncodeWebSafe(rawMessage.join("\r\n"), Utilities.Charset.UTF_8)
  }, "me");
}