Getting data to process from another Numbers doc


#1

Hello

I wonder if this is something that can be automated. Working in Numbers I have to keep track of various metadata for cues including assigning new ISRC codes. What I do is this: Start a new Numbers Doc and fill in the metadata provided by the composers. I then have to open the last Numbers doc I finished (not necessarily the very last Numbers doc I opened), scroll to the bottom of the table and get the last ISRC code assigned. I either copy that number (or just remember it) and add that to the new doc but advancing it by one - so as not to duplicate the last number. Does this make sense?

What I would like is automation that would ask me to choose which document to pull from, look at the last ISRC column (which is always in the same column of the first table on the first sheet) copy that code (which is formatted like ABCYUD1801459), add one to the number, then put that new ISRC code into the new/open spreadsheet/table in the first cell. I can then fill down and be all set. It would be ok if it would just advance the number and copy it - then I could manually paste it in the cell I need.

This would save me the hassle of doing all this manually and I think it would be more accurate in the long run, not relying on my memory or forgetting to add one.

I used to keep all the collection is one large spreadsheet, but it got so big with so many columns, that is was hard to process just the rows I needed and it was clogging up my other Numbers formulas and automation stuff. And I have another AppleScript that needs the collections to be in their own spreadsheet, so I can’t go back to one large one.

What do you think is the best way to begin tackling this project?
Thanks


#2

Have you looked at using Database software such as AirTable instead? They can auto increment numbers, and you could have all of your entries in one table which would allow you to filter and search in just one place.


#3

Yes, exactly, this sounds like a task for AirTable or Filemaker.

I’d pick FileMaker for its scriptability, if your budget allows.


#4

I will look into them, but I have many formulas in Numbers to creat various CSV files I need for submission to publishers.

Can Airtable export to CSV?


#5

Yes! Any good database system or software should have that option.


#6

Ok so Airtable looks interesting. The big question is is it worth it to convert all my spreadsheet functions and formulas and workflow to Airtable for this one thing. Something to think on.

So, is this really a complicated thing to do in AppleScript? As I keep thinking on this, maybe it will be worth the change over. But the more I think on the possibilities, the more I come up with things I can see using automation for. So I’m still thinking AppleScript is the way.

Thanks.


#7

What if you had a small text file that you used to hold your last number and just read & incremented that?

That should remove some of the complexity. Rather than dealing with getting the number from within a cell on a particular sheet in a varying spreadsheet you read and write the content of a fixed location & named file.


#8

That might work, but it adds another step to keep the Text file up to date. I am really hoping to keep the number in just one place, as it must always be a unique number and not have two songs assigned the same ISRC code.

Thanks- I’ll keep searching for a way to do this. It isn’t terrible now to do it manulaly, just thinking through my workflows.

Cheers


#9

The script would keep the text file up to date. The number of steps is in effect one more, but I strongly suspect significantly easier to apply than dynamically identifying a different spreadsheet file to read from each time meaning that the step to retrieve and update the number is likely to be simpler and take fewer lines of code than one to identify and open the correct preceding spreadsheet, and then extrapolate the numeric portion of the ID to use with the new spreadsheet.

Yes, you are duplicating the data, but in practical terms your processing would run faster and be easier to maintain. In data structure design principles you strive to store data uniquely, but the caveat is for cases such as these where performance and maintainability become a key consideration.

This approach provides no more or less security over the uniqueness than a spreadsheet only solution; though if you made it an index file listing all entries rather than just a counter tracking file that would.

Hope that clarifies the proposed approach in relation to the point you raised.


#10

Thanks! I think I kind of understand what you are saying. The problem is I have no idea how any of this works, just lots of ideas of how I might use some “magic”

I think what I need is to find a good AppleScript/Keyboard Maestro Automation consultant to chat with and see what is and is not “easily” accomplished. I don’t really have the time to learn all this stuff to make it work, and maybe I need to hire it out, and then learn a bit here and there.

Cheers!


#11

If you do that I’d recommend getting someone to migrate everything over to a database system - this will give you lots of automation possibilities in the future as well as plenty of export options.


#12

FileMaker gets your database and AppleScript as well as native FM scripting.

But you really want to stay in Numbers, here is some AppleScript (actually mostly bash called from AppleScript to maintain an auto incrementing ID # (that I wrote before I abandoned NVAlt).

set theID to (do shell script "cat ~/Dropbox/texts/nv/_counter.txt")
set n to (theID as number) + 1
do shell script ("echo \"" & (n as string) & "\" > ~/Dropbox/texts/nv/_counter.txt")

#13

Without detracting from what everyone has said about database software, I think it would definitely be doable with AppleScript. You could save your formula in a Numbers template and have a script that looks something like this:

property theTemplate : "custom_template_name" -- change this to be your template's name

-- get the file for the existing spreadsheet
set oldSpreadsheet to choose file with prompt "Choose the existing spreadsheet you want the new one to continue from." of type {"Numbers"}

-- get the old ISRC number from the existing spreadsheet
tell app "Numbers"
activate
set oldDoc to open oldSpreadsheet

-- you'd have to customise this next bit depending on what the column's name is
set theNumber to the formatted value of the last cell of column "ISRC" of table 1 of sheet 1 of oldDoc
set theText to text 1 through 6 of theNumber -- not necessary if these letters are always the same
set theNumber to text 7 through -1 of theNumber -- to strip out the letters at the beginning
set theNumber to (theNumber as number) + 1 -- to increment it
set theNumber to theText & theNumber -- to combine it with the letters again

-- now make the new spreadsheet
set newDoc to make new document with properties {document template:template theTemplate}

-- now insert theNumber
tell table 1 of sheet 1 of newDoc
set the value of cell (header row count + 1) to theNumber
end tell
end tell