Importing software license keys from excel? (or Applescript help)

[itss]

hey all,

Is there any way to import license keys from a spreadsheet or .csv file? I'm in the process of setting up License Management in our JSS and I don't look forward to the amount of copying and pasting I will be doing. We have many hundreds (perhaps thousands) of license keys stored in various excel files.

I've started to write an Applescript (which I have practically no experience in) that works it's way down a spreadsheet copying the keys to a variable and inputting the values into the JSS webform, but I can't figure out how to tell it to click the "store licenses" button.

It would be nice if I could attach a screenshot to show you, but if you go to the "editlicensedsoftware.html" page in the JSS, and click the "licenses" tab and then view source, the button is on line 774.

If someone can help me, then I'd be happy to share the script when it's done...

[itss]

Nevermind, I've got it.

[blindauer]

Can you expand on this? What did you end up doing. I'm curious to do something similar.

[itss · Accepted solution]

It's not elegant, but I scripted it so if you've got the excel spreadsheet open on your desktop and the Casper web interface open (in Safari) to the page that lists all of the licenses for a piece of software ("settings" tab -> "Licensed software management" -> click "edit" for a piece of software -> "licenses" sub-tab) then the script will run down the Excel column and copy the value of a cell into a variable, then go to Safari, click the "add licenses" link, fill in the web form (pasting the value of the variable into the approprate text box), and then click the "store license" button. It keeps doing this until it reaches the specified bottom cell.

I did this in Applescript mostly because it was easy to learn. One of my co-workers said it would have been easier to do in Javascript...

The problem I ran into with the "Store Licenses" button was due to a missing semicolon in the source javascript for the button. Apparently Applescript is much less forgiving of typos than Javascript is.

I probably could have made it open the right page in Safari and even request the user browse for the spreadsheet, prompt for top cell and bottom cell in the column etc., but I didn't...

If you're interested, here is the Applescript I'm using:

-- the "myCol" variable stores the Excel column number where the serial keys are kept. It will need to be changed to match the relavent column in your spreadsheet
set myCol to 3
-- the "myRow" variable stores the starting row. It will need to be changed to match the starting row in your spreadsheet
set myRow to 2
-- the repeat loop below defines how many times the loop is run. Because of the syntax, the number must be changed to the final row plus 1 (sorry)
repeat while myRow < 31 tell application "Microsoft Excel" copy value of (cell myCol of row myRow) to myVal end tell tell application "Safari" activate do JavaScript "submitForm('Add License');" in document 1 delay 1 tell application "System Events" keystroke tab delay 0.1 keystroke tab delay 0.1 -- here is where the serial number goes into the first text box keystroke myVal as text delay 0.1 keystroke tab delay 0.1 -- if I was using software that required 2 serial numbers, below is the input for the second text box (currently blank) keystroke tab delay 0.1 -- 3rd text box (Organization name) keystroke "Your organization name goes here" delay 0.1 keystroke tab delay 0.1 -- below is the input for "Registered To:" (currently blank) keystroke tab delay 0.1 -- below is the input for the number of licenses per key (currently 1) keystroke "1" delay 1 end tell tell application "Safari" do JavaScript "submitForm('Store License');" in document 1 end tell end tell set myRow to myRow + 1 delay 1
end repeat