Saving Variables from User Input Prompt in a Spreadsheet

Hi!

I'm wondering if anyone has suggestions as to the easiest way to save the variables I get from the "User Input" prompt into a spreadsheet so that every time I run the prompt it saves the data in a new row?

I am using the User Input prompt as a Client/Project intake form and would like to create a running ledger of each entry.

I had messed around with an AppleScript transferring the data to Numbers but it was clunky and stopped working for reasons I couldn't figure out.

Ideally the new info would get added to the spreadsheet without having to actually open the spreadsheet every time.

Google sheets API seems to have that functionality but the coding is waaaayyyyy beyond me.

Maybe Excel would work too? I don't have Excel but would get it if that was the best option.

Any help would be greatly appreciated.

Thanks!

Do you need to be able to modify the spread sheet as well as add rows to it?

If not, add the data to a CSV text file, and then simply open the CSV in a spread sheet if you want to look at the data.

Depending on the data, you may have to quote the fields appropriately for it to work, otherwise simply writing something like:

%Variable%One%,%Variable%Two%,%Variable%Three%%Return%

using the Append Text to a File action will work.

If the fields have quotes, commas, or other non-alphanumeric characters then you will definitely need to do a bit more work (I want to add better support for reading/writing CSVs in future versions of Keyboard Maestro).

2 Likes

What Peter said...

I looked at scripting Numbers, and you're right – it's horribly clunky.

Microsoft Excel is not nearly so awful to script, but you would have to have it running while you were scripting it.

Although... I seem to remember that there's a Python library out there somewhere that will let you add data directly to Excel files.

The simplest, most maintainable solution for you is going to be a .csv file. You can Quick Look those in the Finder, and they're easily imported into a spreadsheet.

1 Like

Post what you have, along with a (zipped) sanitised example of the Numbers doc -- there's a good chance someone will see what the "stopped working" reason is, eg you got to end of the default amount of empty rows Numbers gives you and need to start explicitly adding more.

Thank you so much! I really appreciate you taking the time to respond. I will try this!

What would you suggest if I wanted to modify the spread sheet? eventually I would want to be able to create invoices for each entry in the ledger and have a column indicating the status of the payment ("pending", "Paid in full", etc...).

adding data directly to the the file without opening the spreadsheet would be nice but not a requirement if it makes creating a modifiable spreadsheet more difficult.

Thanks!!!

Thank you too for the response! I will try this and see what happens. as I mentioned in my reply to Peter, what would you suggest if I wanted to modify the spreadsheet?

Thanks!

1 Like

I would suggest you do it the same way, adding rows to the CSV.

Separately, have a spread sheet, but leave the left columns as untouchable. Then to update just open the CSV as a spread sheet, copy all the rows and columns, and paste it in to the left columns of your spread sheet (and extend the right extra columns down to include the new rows).

Automating that process would be a pain, but it's relatively straight forward to do manually.

You may also be able to take your extended spread sheet, export it as a CSV, look to see what exactly the right columns contain, and include that in your output. I'm not sure if that will work with calculations and such, but it might.

1 Like

Thank you!

1 Like