Skip to content

Instantly share code, notes, and snippets.

@binaryatrocity
Created January 20, 2019 17:56
Show Gist options
  • Save binaryatrocity/33c00fa11a0a19a2b8098e6eaebc93aa to your computer and use it in GitHub Desktop.
Save binaryatrocity/33c00fa11a0a19a2b8098e6eaebc93aa to your computer and use it in GitHub Desktop.
Google App Script for Fencing item spreadsheet
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Fencing Tools')
.addItem('Parse Output', 'parseFenceOutput')
.addItem('Clear Inputs', 'clearFenceInput')
.addToUi();
}
function onEdit(e){
var range = e.range;
var sheet = e.source.getActiveSheet();
// Is this our Input sheet and the A column? Was the edit clearing the cell?
if(sheet.getName() != "Input" || range.getColumn() != 1 || range.isBlank()) { return; }
var date = new Date();
var time = date.toLocaleString('en-US', { hour: 'numeric', hour12: true, minute: 'numeric' })
var tzinfo = date.toLocaleTimeString('en-us',{timeZoneName:'short'}).split(' ')[2];
//var date_string = (date.getMonth()+1) + '/' + date.getDate() + ', ' + time + ' [' + tzinfo + ']';
//var date_string = time + ' [' + tzinfo + ']';
sheet.getRange('C'+range.getRow()).setValue(time);
}
function clearFenceInput() {
var spreadsheet = SpreadsheetApp.getActive();
var input_sheet = spreadsheet.getSheetByName('Input');
input_sheet.getRange('A2:A13').clearContent();
input_sheet.getRange('C2:C13').clearContent();
}
function parseFenceOutput() {
var spreadsheet = SpreadsheetApp.getActive();
// Grab our sheets by name
var input_sheet = spreadsheet.getSheetByName('Input');
var output_sheet = spreadsheet.getSheetByName('Outputs');
// Grab the data from A1
var inputs = input_sheet.getRange('A2:A13').getValues();
// Regex to handle each fence's response
//const regex = /^([A-Z][\w\s]+) whispers to you( in .+?| with a [\w\s]+ accent)?: (At the moment, the market is hot for |I've got a buyer interested in |I'm interested in )(.+?)(, and I'm willing to pay more than usual for them\.|, and will pay more than my usual for them\.|\.)$/;
const regex = /^([A-Z][\w\s]+) whispers to you( in .+?| with a [\w\s]+ accent)?: (At the moment, the market is hot for |I've got a buyer interested in |I'm interested in |At the moment, |Seems that )(.+?)(, and I'm willing to pay more than usual for them\.|, and will pay more than my usual for them\.| are selling like hot cakes\. I'd be willing to pay top price for them\.| are all the rage right now\. {1,2}If you can get some I'd be happy to take them off your hands\.|\.)$/;
var fenced_objects = [];
// Loop over our returned cells and process
for(var i = 0; i < inputs.length; i++) {
if(!regex.test(inputs[i])) {
// No match? skip this one
console.log('Input cell did not match regex!');
continue;
}
var input = inputs[i][0];
var result = input.match(regex);
// Split the item list
var split_objects = result[4].split(', ');
split_objects = split_objects.concat(split_objects.pop().split(' and '));
for(var k = 0; k < split_objects.length; k++) {
fenced_objects.push([split_objects[k],result[1]]);
}
}
// Clear the output sheet before writing
output_sheet.getRange('A3:B').clearContent();
// Write our items to the output sheet!
for(var i = 0; i < fenced_objects.length; i++) {
output_sheet.getRange('A'+(i+3)).setValue(fenced_objects[i][0]);
output_sheet.getRange('B'+(i+3)).setValue(fenced_objects[i][1]);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment