metadata work

tradingcardclub-metadata.js

function onOpen() {
  /****
   * Start of multi-level dropdowns
   ****/
  var ws = "";
  var wsOptions = "";
  var options = "";
  // script variables
  function setVars() {
    var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
    var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
      optionsWsName
    );
    var options = wsOptions
      .getRange(2, 1, wsOptions.getLastRow() - 1, 4)
      .getValues();
  }

  /****
   * End of multi-level dropdowns
   ****/

  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu("Custom Menu")
    .addItem("Export Owned Cards to the Database", "exportOwnsToMongoDB")
    .addItem("Export Wanted Cards to the Database", "exportWantsToMongoDB")
    .addItem(
      "Remove Cards in Deleted from the Database",
      "removeEventsFromMongoDB"
    )
    .addItem("Import Card Metadata", "displayData")
    .addItem("Cache Card Metadata", "testGetCacheFrom")
    .addSeparator()
    .addSubMenu(ui.createMenu("Sub-menu").addItem("Second item", "menuItem2"))
    .addToUi();
}

/****
 * Start of import card metadate from Mongo
 ****/

function displayData() {
  SpreadsheetApp.getUi().alert("GETTING DATA");
  // Call the API
  var response = UrlFetchApp.fetch(
    "https://webhooks.mongodb-realm.com/api/client/v2.0/app/cards-fvyrn/service/Cards/incoming_webhook/getCardMetadata"
  );

  // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("CardBrands");
  //var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  //var sheet = spreadsheet.getSheetByName("foo");
  var outputData = [["Year", "Manufacturer", "Brand", "Variation"]];
  data.forEach((r) => {
    outputData.push([r.year, r.manufacturer, r.brand, r.variation]);
  });
  var newLine = sheet.getLastRow() + 1;
  var newRange = sheet.getRange(
    newLine,
    1,
    outputData.length,
    outputData[0].length
  );
  newRange.setValues(outputData);

  //var sortRange = sheet.getDataRange();
  //sortRange = newRange.offset(1,0);
  sortRange = newRange.offset(1, 0, newRange.getNumRows() - 1);
  // Sorts the sheet by the first column, ascending
  sortRange.sort(1);
}

/****
 * End of import card metadate from Mongo
 ****/
/****
 * Start of cache card data
 ****/

function testGetCacheFrom() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    "CardBrands"
  );
  var data = sheet.getDataRange().getValues();

  //OR use getRange from below
  // var cards = wsOptions
  //   .getRange(2, 1, wsOptions.getLastRow() - 1, 4)
  //  .getValues();
  var chunky = ChunkyCache(CacheService.getDocumentCache(), 1024 * 90);
  chunky.put("Data", data, 7200); //set cache for 2 hours.
  var check = chunky.get("Data");
  SpreadsheetApp.getUi().alert("FINISHED");
  //var sheetPut = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Out');
  // for (c in check) {
  //   sheetPut.appendRow(check[c]);
  // }
}

/****
 * End of cache card data
 ****/

/****
 * Michael Lynn - http://blog.mlynn.org
 * Stitching Sheets - Integrating Google Sheets with MongoDB Using MongoDB Stitch
 ****/

// Create an object which contains keys for each column in the spreadsheet
var columns = {
  // 0 indexed
  year: 0,
  player: 1,
  manufacturer: 2,
  brand: 3,
  series: 4,
  variation: 5,
  type: 6,
  career_stage: 7,
  team: 8,
  card_number: 9,
  print_run: 10,
  number: 11,
  image_path: 12,
  status: 13,
  forTrade: 14,
  purchase_date: 15,
  card_id: 16,
};

/****
 * Export the events from the sheet to a MongoDB Database via Stitch
 ****/
function exportOwnsToMongoDB() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Own");
  var headerRows = 1; // Number of rows of header info (to skip)
  var range = sheet.getDataRange(); // determine the range of populated data
  var numRows = range.getNumRows(); // get the number of rows in the range
  var data = range.getValues(); // get the actual data in an array data[row][column]
  var userEmail = Session.getActiveUser().getEmail();
  var userName = Session.getActiveUser()
    .getEmail()
    .substring(0, Session.getActiveUser().getEmail().indexOf("@"));

  for (var i = headerRows; i < numRows; i++) {
    var player = data[i][columns.player];
    // var date_start = Utilities.formatDate(new Date(data[i][columns.date_start]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    // var date_end = Utilities.formatDate(new Date(data[i][columns.date_end]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    // Make a POST request with form data.
    var formData = {
      user: userEmail,
      userName: userName,
      year: data[i][columns.year],
      player: data[i][columns.player],
      manufacturer: data[i][columns.manufacturer],
      brand: data[i][columns.brand],
      series: data[i][columns.series],
      variation: data[i][columns.variation],
      type: data[i][columns.type],
      career_stage: data[i][columns.career_stage],
      team: data[i][columns.team],
      card_number: data[i][columns.card_number],
      print_run: data[i][columns.print_run],
      number: data[i][columns.number],
      image_path: data[i][columns.image_path],
      forTrade: data[i][columns.forTrade],
      status: data[i][columns.status],
      purchase_date: new Date(data[i][columns.purchase_date]),
      card_id: data[i][columns.card_id],
    };
    var options = {
      method: "post",
      payload: formData,
    };
    if (player) {
      //var insertID = UrlFetchApp.fetch('https://webhooks.mongodb-stitch.com/api/client/v2.0/app/stitch-sheets-zkvuv/service/sheets/incoming_webhook/import', options);
      var insertID = UrlFetchApp.fetch(
        "https://webhooks.mongodb-realm.com/api/client/v2.0/app/cards-fvyrn/service/AddCards/incoming_webhook/AddCards",
        options
      );

      //eventIdCell.setValue(insertID); // Insert the new event ID
    }
  }
}

function exportWantsToMongoDB() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Want");
  var headerRows = 1; // Number of rows of header info (to skip)
  var range = sheet.getDataRange(); // determine the range of populated data
  var numRows = range.getNumRows(); // get the number of rows in the range
  var data = range.getValues(); // get the actual data in an array data[row][column]
  var userEmail = Session.getActiveUser().getEmail();
  var userName = Session.getActiveUser()
    .getEmail()
    .substring(0, Session.getActiveUser().getEmail().indexOf("@"));

  for (var i = headerRows; i < numRows; i++) {
    var player = data[i][columns.player];
    // var date_start = Utilities.formatDate(new Date(data[i][columns.date_start]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    // var date_end = Utilities.formatDate(new Date(data[i][columns.date_end]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    // Make a POST request with form data.
    var formData = {
      user: userEmail,
      userName: userName,
      year: data[i][columns.year],
      player: data[i][columns.player],
      manufacturer: data[i][columns.manufacturer],
      brand: data[i][columns.brand],
      series: data[i][columns.series],
      variation: data[i][columns.variation],
      type: data[i][columns.type],
      career_stage: data[i][columns.career_stage],
      team: data[i][columns.team],
      card_number: data[i][columns.card_number],
      print_run: data[i][columns.print_run],
      number: data[i][columns.number],
      image_path: data[i][columns.image_path],
      forTrade: data[i][columns.forTrade],
      status: data[i][columns.status],
      purchase_date: new Date(data[i][columns.purchase_date]),
      card_id: data[i][columns.card_id],
    };
    var options = {
      method: "post",
      payload: formData,
    };
    if (player) {
      //var insertID = UrlFetchApp.fetch('https://webhooks.mongodb-stitch.com/api/client/v2.0/app/stitch-sheets-zkvuv/service/sheets/incoming_webhook/import', options);
      var insertID = UrlFetchApp.fetch(
        "https://webhooks.mongodb-realm.com/api/client/v2.0/app/cards-fvyrn/service/AddCards/incoming_webhook/AddCards",
        options
      );

      //eventIdCell.setValue(insertID); // Insert the new event ID
    }
  }
}

/****
 * Delete the events from the Calendar and remover the eventID Reference from the sheet - wipeout.
 ****/
function removeEventsFromMongoDB() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Delete");
  var headerRows = 1; // Number of rows of header info (to skip)
  var range = sheet.getDataRange(); // determine the range of populated data
  var numRows = range.getNumRows(); // get the number of rows in the range
  var data = range.getValues(); // get the actual data in an array data[row][column]
  var userEmail = Session.getActiveUser().getEmail();
  var userName = Session.getActiveUser()
    .getEmail()
    .substring(0, Session.getActiveUser().getEmail().indexOf("@"));

  for (var i = headerRows; i < numRows; i++) {
    // Cells are 1 indexed
    var player = data[i][columns.player];
    //var eventIdCell = range.getCell(i+1, columns.card_id+1);
    // Make a POST request with form data.
    var formData = {
      user: userEmail,
      card_id: data[i][columns.card_id],
    };
    var options = {
      method: "post",
      payload: formData,
    };
    //var insertID = UrlFetchApp.fetch('https://webhooks.mongodb-stitch.com/api/client/v2.0/app/stitch-sheets-zkvuv/service/sheets/incoming_webhook/remove', options);
    var insertID = UrlFetchApp.fetch(
      "https://webhooks.mongodb-realm.com/api/client/v2.0/app/cards-fvyrn/service/RemoveCards/incoming_webhook/removeCards",
      options
    );
    //eventIdCell.setValue(""); // Insert the new event ID

    sheet.deleteRow(i + 1);
    //  for (var i = row_del.length - 1; i>=0; i--) {
    //  sheet.deleteRow(row_del[i]);
    //}
  }
}

function onEdit(event) {
  var mainWorkSheet = "Targets";
  var dataValidationSheet = "CardBrands";
  var firstLevelColumn = 1;
  var secondLevelColumn = 3;
  var thirdLevelColumn = 4;
  var fourthLevelColumn = 5;
  var chunky = ChunkyCache(CacheService.getDocumentCache(), 1024 * 90);
  //define filtered arrays for reuse
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    "CardBrands"
  );
  var s = event.source.getActiveSheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var r = event.source.getActiveRange();
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWorkSheet);
  var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    dataValidationSheet
  );
  // var options = wsOptions
  //   .getRange(2, 1, wsOptions.getLastRow() - 1, 4)
  //   .getValues();
  var options = chunky.get("Data");

  /****
   * Move Rows
   ****/

  if (r.getColumn() == 14 && r.getValue() == "Option") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Options");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

  // move rows from Wants to Delete when the value is set
  //if(s.getName() == "Want" && r.getColumn() == 14 && r.getValue() == "Delete") {
  if (r.getColumn() == 14 && r.getValue() == "Delete") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Delete");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

  // move rows from Options to Want when the value is set
  if (r.getColumn() == 14 && r.getValue() == "Want") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Want");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

  // move rows from Want to Options when the value is set
  if (r.getColumn() == 14 && r.getValue() == "Option") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Options");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

  // move rows from Want to Own when the value is set
  if (r.getColumn() == 14 && r.getValue() == "Own") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Own");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
  // move rows from Own to Sold when the value is set
  if (r.getColumn() == 14 && r.getValue() == "Sold") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Sold");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

  // move rows  to Target when the value is set
  if (r.getColumn() == 14 && r.getValue() == "Target") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Targets");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
  /****
   * End Move Rows
   ****/

  /****
   * Start of multi-level dropdowns in onEdut function
   ****/
  var activeCell = event.range;
  var val = activeCell.getValue();
  var row = activeCell.getRow();
  var cell = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  if (wsName === mainWorkSheet && cell === firstLevelColumn && row > 1) {
    applyFirstLevelValidation(val, row);
  } else if (
    wsName === mainWorkSheet &&
    cell === secondLevelColumn &&
    row > 1
  ) {
    applySecondLevelValidation(val, row);
  } else if (wsName === mainWorkSheet && cell === thirdLevelColumn && row > 1) {
    applyThirdLevelValidation(val, row);
  }

  /****
   * End of multi-level dropdowns in onEdit function
   ****/

  /****
   * End of multi-level dropdowns
   ****/

  /****
   * Start of validation functions
   ****/

  function applyFirstLevelValidation(val, r) {
    SpreadsheetApp.getUi().alert("IN applyFirstLevelValidation FUNC " + val);

    if (val === "") {
      // ws.getRange(r, secondLevelColumn).clearContent();
      //ws.getRange(r, secondLevelColumn).clearDataValidations();
      // ws.getRange(r, thirdLevelColumn).clearContent();
      //  ws.getRange(r, thirdLevelColumn).clearDataValidations();
      //  ws.getRange(r, fourthLevelColumn).clearContent();
      //  ws.getRange(r, fourthLevelColumn).clearDataValidations();
    } else {
      //   ws.getRange(r, secondLevelColumn).clearContent();
      //   ws.getRange(r, thirdLevelColumn).clearContent();
      //  ws.getRange(r, fourthLevelColumn).clearContent();
      //SpreadsheetApp.getUi().alert("rawOptions "+ JSON.stringify(options));
      //TODO - STORE THIS FILTEREDOPTIONS ARRAY OUTSIDE THE FUNCTION SO IT CAN BE USED IN SUBSEQUENT VALIDATION FUNCTIONS TO SAVE TIME

      var firstLevelFilteredOptions = options
        .filter((set) => set[0] === val)
        .sort();
      //var firstLevelFilteredOptions = options.filter(function (o) {
      //  return o[0] === val;
      // });

      SpreadsheetApp.getUi().alert(
        "firstLevelFilteredOptions " + JSON.stringify(firstLevelFilteredOptions)
      );
      // var listToApply = filteredOptions.map(function (o) {
      //   return o[1];
      // });
      var listToApply = firstLevelFilteredOptions
        .map((cardSet) => cardSet[1])
        .filter((item, index, array) => {
          return array.indexOf(item) === index;
        });

      SpreadsheetApp.getUi().alert(
        "FirstLevelValidation - listToApply " + JSON.stringify(listToApply)
      );
      var cell = ws.getRange(r, secondLevelColumn);
      applyValidationToCell(listToApply, cell);
    }
  }

  function applySecondLevelValidation(val, r) {
    if (val === "") {
      // ws.getRange(r, thirdLevelColumn).clearContent();
      // ws.getRange(r, thirdLevelColumn).clearDataValidations();
      //  ws.getRange(r, fourthLevelColumn).clearContent();
      //   ws.getRange(r, fourthLevelColumn).clearDataValidations();
    } else {
      //   ws.getRange(r, thirdLevelColumn).clearContent();
      //    ws.getRange(r, fourthLevelColumn).clearContent();
      var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
      //var filteredOptions = options.filter(function (o) {
      //  return o[0] === firstLevelColValue && o[1] === val;
      // });

      // var secondLevelFilteredOptions = firstLevelFilteredOptions.filter(function (o) {
      //  return o[1] === val;
      //});
      //SpreadsheetApp.getUi().alert("firstLevelFilteredOptions - STORED "+ firstLevelFilteredOptions);
      // secondLevelFilteredOptions = firstLevelFilteredOptions.filter(set => set[1] === val);
      var secondLevelFilteredOptions = options
        .filter((set) => set[0] === firstLevelColValue && set[1] === val)
        .sort();

      SpreadsheetApp.getUi().alert(
        "secondLevelFilteredOptions " + secondLevelFilteredOptions
      );
      // var listToApply = filteredOptions.map(function (o) {
      //   return o[2];
      //});

      var listToApply = secondLevelFilteredOptions
        .map((cardSet) => cardSet[2])
        .filter((item, index, array) => {
          return array.indexOf(item) === index;
        });

      SpreadsheetApp.getUi().alert(
        "SecondLevelValidation - listToApply " + listToApply
      );
      var cell = ws.getRange(r, thirdLevelColumn);
      applyValidationToCell(listToApply, cell);
    }
  }

  function applyThirdLevelValidation(val, r) {
    SpreadsheetApp.getUi().alert("thirdLevelValidation Triggered ");
    if (val === "") {
      //   ws.getRange(r, fourthLevelColumn).clearContent();
      //   ws.getRange(r, fourthLevelColumn).clearDataValidations();
    } else {
      //   ws.getRange(r, fourthLevelColumn).clearContent();
      var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
      var secondLevelColValue = ws.getRange(r, secondLevelColumn).getValue();
      //thirdLevelValidationFilteredOptions
      // var filteredOptions = options.filter(function (o) {
      //   return (
      //     o[0] === firstLevelColValue &&
      //     o[1] === secondLevelColValue &&
      //      o[2] === val
      //    );
      //   });

      var thirdLevelValidationFilteredOptions = options
        .filter(
          (set) =>
            set[0] === firstLevelColValue &&
            set[1] === secondLevelColValue &&
            set[2] === val
        )
        .sort();
      SpreadsheetApp.getUi().alert(
        "thirdLevelValidationFilteredOptions " +
          thirdLevelValidationFilteredOptions
      );

      // var listToApply = filteredOptions.map(function (o) {
      // return o[3];
      //});
      var listToApply = thirdLevelValidationFilteredOptions
        .map((cardSet) => cardSet[3])
        .filter((item, index, array) => {
          return array.indexOf(item) === index;
        });
      SpreadsheetApp.getUi().alert(
        "thirdLevelValidationFilteredOptions - listToApply " + listToApply
      );

      var cell = ws.getRange(r, fourthLevelColumn);
      applyValidationToCell(listToApply, cell);
    }
  }

  function applyValidationToCell(list, cell) {
    var rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(list)
      .setAllowInvalid(false)
      .build();

    cell.setDataValidation(rule);
  }

  /****
   * End of validation functions
   ****/

  /****
   * Start of create uniqueID
   ****/

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sheetVals = [["Targets", "cardid-", "", 17, 9]];

  //Loop through every sheet value
  for (var i = 0; i < sheetVals.length; i++) {
    var sheetID = sheetVals[i][0],
      frontStr = sheetVals[i][1],
      backStr = sheetVals[i][2],
      IDcol = sheetVals[i][3],
      editCol = sheetVals[i][4];

    var offset = IDcol - editCol;

    if (sheet.getName() === sheetID) {
      var date = new Date().valueOf();
      var newID = frontStr + date + backStr;

      //Check the location of the active cell
      var selectedCell = ss.getActiveCell();
      if (selectedCell.getColumn() === editCol) {
        //Update the ID Column
        var cellToChange = selectedCell.offset(0, offset);
        if (cellToChange.isBlank()) {
          cellToChange.setValue(newID);
        }
      }
    }
  }

  /****
   * End of create uniqueID
   ****/
} // end of onEdit function