assasinmonkey
patreon
I've been scripting a Silent Auction Google Form
🕑 Added 2021-06-12 01:35:56 +0000 UTC... yeah, what the title says, and I've been spending way too many hours already haha. Or maybe just enough, because it might just be useful?!
Bit of technical talk below, but I would also be interested in hearing some feedback or ideas for info/questions Auctions typically have if anyone has done/filled in some before. I still gotta make myself a list of what to link or how/where (such as artist TOS, or what's auctioned, etc.)
I had to spend a lot of time brushing up on script since the last time I scripted in Flash 3.0 or something. Basically, learning GoogleScript(Javascript) from scratch. A lot of trial and error, researching, and finding out what I can and can't do with the way Forms and Spreadsheets work in Google.
It started out simple enough, but maybe anyone who has programmed/scripted knows that's never the end of it. I've worked closely with programmers enough to know that, and I'm totally one who'd feed the Feature Creep.
Started out as just automatically showing the highest bid in the Form itself, updated after someone submits a bid. (Can't run any live scripts actively in the form, only update on triggers such as Submit, or Editor actions)
This turned into trying to clean it up, but then I just added stuff too to try and improve the use experience. Had to make a compromise pretty early on with some Form limits, tho.
I could either allow folks to enter too low bids, or only allow whole numbers. I decided to stick with whole numbers. Each question in the form can have only one 'Validation', and both of those take up the same spot. That was already after I spend time figuring out how to update the 'Validation' too... While 'Whole Numbers' I only set once per form.
And then I made it so I automatically send an E-Mail, and the type of E-Mail depends if your bid failed or was accepted. I even alter the message if your bid is equal to an existing one, but failed because someone got it first. Decent chunk of the time and script went into making that part automated. Tho again... probably also because I was trying to learn and fully understand what was even going on. I tried not to just copypasta code in, but use it as reference to rewrite each part myself.
For example, there was a part where I had to find out how to look up the IDs of different questions in the Form, so I could find and change the one listing the Top Bid. Google had some existing code to fetch and log that info. I could've just used theirs, but I wanted to understand it so I could find some other IDs if needed. Such as the answers instead of the questions for sending pre-filled links. But I ended up just having to generate a link with their existing Form features and rip the IDs out of that.
Does mean I managed to also generate a Pre-Filled Form link with most of your previous answers already filled in (the ones that are allowed to be pre-filled). This will be very useful for larger forms that may require submitting Character Ref links for example. So you don't have the enter all of that all over again every single time you make a bid! I could've allowed to edit your bids instead, but that'd also allow folks to basically remove their bids by lowering it. Maybe I could have the spreadsheet solve that by automatically registering each user and their bid somewhere the Form can't change it. But I skipped that.
I definitely had to make some work-arounds where it wouldn't break or just making sure everything runs in the correct order so it finishes with the correct numbers in the right places. Just because of the way you can only run the script when it activates when a user submits. But you can have the script attached to either the form or the spreadsheet. Because I had to move things around back and forth to troubleshoot, I ended up with all my code in the Form's files, but maybe it could do more in the Spreadsheet's?
At the moment I haven't tried applying it to a brand new Form and Sheet. But I tried to make the code a bit manageable and flexible, so it should mostly be just replacing Names and IDs, not much else. Should...
I may ask some friends or mutuals if they had any Auctions planned soon and if they wanna give it a try. Unless I set up something myself first...
If anyone here is interested in trying it out or wants to see the actual script/code, let me know. Are there actually some of y'all who work with Javascript here? (GoogleScripts)
To try and sum up the features so far:
- Form auto updates on submitting, replacing item Title with newest Top Bid
- Ability to automatically form and send E-Mails
- Can send different E-Mail depending on failed or approved bid
- Put together a Pre-Filled Form for easy re-bid (No need to fill most info again)
- Made special E-Mail line that only sends when equal to existing highest bid (first come first serve)
- All code condensed into one file, to prevent updates going out of order and breaking
- Simple auto sort Highest to lowest bid in Spreadsheet on submit (Owner/Editor only)
Dead-ends/impossible things/options/difficulties
- Can't update the forms live, only when user submits or editor manually updates
- Impossible to have multiple 'Validates' on a single answer. Meaning you can't both require whole numbers and limit only to Higher than bid.
- Chose Whole numbers to prevent cent bidding
- Additionally, can't force user to submit in increments of 5 or 10. Can still suggest and manually eliminate those bids.
- Maybe possible by intercepting and removing them in the Spreadsheet? Sheets might be able to do this, but not sure if compatible enough with existing code.
- Still have to gather more info/questions that fit an Auction form. Will have to investigate, but shouldn't interfere (much) with code.
- E-Mails could be expanded. HTML body is already used, only basics (Bold, Underline, Hyperlink). It's possible to embed your own files, data, and attachments. Possible image of art can be attached. But might bloat the code.
- I had to use a small workaround with two cells in Spreadsheet, instead of one, to allow different E-Mails types to be send out. Needed to track current highest and previous highest bid, because the form Submits to the spreadsheet before the script runs. Basically means I wouldn't be able to check if your bid is higher than the highest, because it'd be updated already with your bid.
