Query Steam CD Keys for activation on Steamworks in Google Spreadsheet Apps Scripts.

SteamCDKeyActivated.gs

// Inspired from https://gist.github.com/petersvp/270f7d5d7d548448f4897586a0d389c0 and https://gist.github.com/Jimbly/9f6c6a0d9414310347f2803902ac7bb7

// 1. GO TO Steamworks, into the Query CD Key page, here: https://partner.steamgames.com/querycdkey/	// 1. Having a Google Spreadsheet with the following format in a row:
// *Make sure your language is "English".	
//	
// 2. Having a Google Spreadsheet with the following format in a row:
// | KEY | Sent (checkbox) | Note | Activated |
// (You can modify the format by changing the Column constants.)
//
// 3. Call validateKeyActivated_All or validateSheetKeyActivated_ActiveSheet.
//
// 4. Fill the Cookie from broswer.
// To find the Cookie, you can see this comment: 
// https://gist.github.com/Jimbly/9f6c6a0d9414310347f2803902ac7bb7?permalink_comment_id=2554348#gistcomment-2554348


/** @OnlyCurrentDoc */
// The sheet names you are gonna query for validateKeyActivated_All function.
const QuerySheets = [
  "Steam Main App Keys",
  "Steam Demo Keys"
]

// The format of the sheet.
const ColumnKey = 1;
const ColumnSent = 2;
const ColumnActivated = 4;

// The cookie will be asked for if it is empty.
var Cookie = "";


// Query all sheets in the QuerySheets list.
function validateKeyActivated_All() {
  if (!askForCookie()) {
    return;
  }

  for (let sheetNameIdx in QuerySheets) {
    const sheetName = QuerySheets[sheetNameIdx];
    if (!validateKeyActivated(sheetName)) {
      return;
    }
  }
}

// Query the active sheet.
function validateSheetKeyActivated_ActiveSheet() {
  if (!askForCookie()) {
    return;
  }

  validateKeyActivated(SpreadsheetApp.getActiveSheet().getName());
}

function validateKeyActivated_IgnoreSent_All() {
  if (!askForCookie()) {
    return;
  }

  for (let sheetNameIdx in QuerySheets) {
    const sheetName = QuerySheets[sheetNameIdx];
    if (!validateKeyActivated(sheetName, true)) {
      return;
    }
  }
}

function validateSheetKeyActivated_IgnoreSent_ActiveSheet() {
  if (!askForCookie()) {
    return;
  }

  validateKeyActivated(SpreadsheetApp.getActiveSheet().getName(), true);
}

// Query the sheet by the specfic name.
function validateKeyActivated(sheetName, ignoreSentToggle = false) {
  if (!askForCookie()) {
    return false;
  }

  Logger.log('Start validating ' + sheetName);
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var lastRow = spreadsheet.getLastRow();
  for (let i = 2; i <= lastRow; i++) {
    let key = spreadsheet.getRange(i, ColumnKey).getValue();
    let sent = spreadsheet.getRange(i, ColumnSent).getValue();
    let activatedCell = spreadsheet.getRange(i, ColumnActivated);

    if (key == "") {
      // early quit.
      break;
    }

    if ((!ignoreSentToggle && !sent) || activatedCell.getValue() != "") {
      continue;
    }
    const response = requestFromSteam(key, Cookie);
    if (response == "ERROR") {
      SpreadsheetApp.getUi().alert("Error happened! it could be the wrong Cookie.");
      return false;
    }
    // Set value.
    activatedCell.setValue(response);
  }
  return true;
}

function askForCookie() {
  // Only ask for cookie if it is empty.
  if (Cookie != "") {
    return true;
  }

  const ui = SpreadsheetApp.getUi();
  const userInput = ui.prompt("Please enter your cookie from https://partner.steamgames.com/querycdkey/cdkey?cdkey=&method=Query \n(It must be English page)");
  const button = userInput.getSelectedButton();

  if (button === ui.Button.CLOSE) {
    return false;
  }

  Cookie = userInput.getResponseText();

  if (Cookie === "") {
    Logger.log("No cookie.");
    ui.alert("No cookie.");
    return false;
  }

  return Cookie != "";
}

function requestFromSteam(key, cookie) {
  const url = 'https://partner.steamgames.com/querycdkey/cdkey?cdkey=' + key + '&method=Query';
  var headers = {
    "Cookie": cookie,
  }
  var options = {
    'method': 'GET',
    'headers': headers
  };
  let response = UrlFetchApp.fetch(url, options);
  let body = response.getContentText();
  let result = body.split('<h2>Activation Details</h2>')[1];
  if (!result) {
    Logger.log('Error quering CD key ' + key);
    return "ERROR";
  }
  result = result.split('</table>')[0];
  result = result.match(/<td>.*<\/td>/g);
  result = result.map(function (line) {
    return line.replace(/<[^>]*>/g, '');
  });
  let active = result[0] === 'Activated';
  if (active) {
    let line = [key, '"' + result[1] + '"'].join('\t');
    Logger.log(line);
    return result[1];
  } else {
    let line = [key, "not activated"].join('\t');
    Logger.log(line);
  }
  return "";
}