snippet.js

try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  } catch (e) {
    Logger.log("Problem with the spreadsheet URL: '" + e + "'");
    Logger.log("Make sure you have correctly copied in your own spreadsheet URL.");
    return;
  }
  var sheet = spreadsheet.getSheets()[0];
  var spreadsheetData = sheet.getDataRange().getValues();
 
  var keywordTextArray = [];
  var keywordLabels = {};
  var sites = [];
  var neededLabels = [];
 
  for(var i=0; i<spreadsheetData[0].length; i++) {
    var siteName = spreadsheetData[0][i];
    if (siteName && siteName != "") {
      sites.push(siteName.trim());
    }
  }
 
  for(var i=0; i<spreadsheetData.length; i++) {
    var keyword = spreadsheetData[0][i];
    if (!keyword || keyword == "") {
      continue;
    } else {
        keyword = keyword.trim().toLowerCase()
    }
 
    keywordTextArray.push(keyword);
    keywordLabels[keyword] = [];
 
    for(var j=0; j<sites.length; j++) {
      var position = parseInt(spreadsheetData[i][j+1],10);
 
      if (isNaN(position) || position < 1 || position > 9) {
        var labelText = sites[j] + " - Off First Page";
      } else {
        var labelText = sites[j] + " - " + position;
      }
 
      keywordLabels[keyword].push(labelText);
      if (neededLabels.indexOf(labelText) < 0) {
        neededLabels.push(labelText);
      }
    }
  }