Archives for October 2015

AppleScript Export to CSV via iWork Numbers

October 24, 2015

Many of us have to consume reference data in our apps provided by clients, business analysts or other team members. We’re unlikely to have them give us a JSON file or an XML property list.

For structured data, I’ve found that setting up a spreadsheet for clients that I can later parse as a CSV file, works great.

Of course, having to open Microsoft Excel or Apple’s iWork Numbers program manually and go through all of the menu commands to perform an export can become tedious when you have a lot of files to process and/or there’s a high frequency with which you receive updates.

As developers, we know the solution to such tedium is to write code to automate these activities.

Fortunately, Apple has AppleScript support in Numbers that lets us get this done.

I’ve created a public Gist with such an AppleScript program that you’ll find helpful to export Excel or Numbers spreadsheets, into CSV documents.

The script is called SpreadsheetExportToCSV.scpt. You can invoke it from the command line as follows:

osascript SpreadsheetExportToCSV.scpt <input file> <output file>

Here’s a more concrete example where my source file (assumed to have one worksheet in it) is specified, along with my output CSV file:

osascript SpreadsheetExportToCSV.scpt "/Users/me/Documents/MySpreadsheet.xlsx" "/Users/me/Documents/Converted/OutputFile.csv"

I find it safer to always places quotation marks around the input and output file paths, so that any spaces or other special characters don’t need to be escaped.

Source

Here’s the script as currently posted in the GitHub Gist:


#! /usr/bin/osascript

(*
---------------------------------------------------------------------------------

 Script: SpreadsheetExportToCSV

 Command-line tool to convert a spreadsheet document to CSV
 This AppleScript is tested with and compatible with Apple iWork Numbers 3.6,
 current as at October 23, 2015.

 Parameters:
 1. Full Path to the input file, including file extension
 2. Full Path to the output file, including file extension

 Example command-line invocation:

    osascript SpreadsheetExportToCSV.scpt "/Users/me/Documents/MySpreadsheet.xlsx" "/Users/me/Documents/Converted/OutputFile.csv"

 The spreadsheet to use as an input file can be an Excel file or a Numbers file.

 Sohail A.
 Blog: http://sohail.io
 Twitter: @idStar

 Creation Date: October 23, 2015
 
---------------------------------------------------------------------------------
*)

global _inputFilePathAlias
global _outputFilePath
global _requestedOptions


(*
 run

 This is our entry point, our main function, where this script 
 begins execution. We call out to helper functions, to modularize
 the design.
*)
on run argv
	-- Ensure our CSV files are encoding with UTF8:
	ensureUTF8Encoding()
	
	-- Parse and determine input/output paths:
	retrieveCommandLineArguments(argv)
	
	-- Perform the actual activation, file open, export and cleanup:
	processSpreadsheet()
	
end run




---------------------- SUPPORTING FUNCTIONS --------------------------

(*
 retrieveCommandLineArguments

 Handles parsing the command line arguments passed to us.
 We return a list, where the first element is the input file
 path as an alias. The second element is the output path,
 as text (as it may not yet exist).
*)
on retrieveCommandLineArguments(command_line_arguments)
	set _inputFilePathAlias to POSIX file (item 1 of command_line_arguments) as alias
	set _outputFilePath to (POSIX file (item 2 of command_line_arguments)) as text
	
	log "input file path is: " & _inputFilePathAlias
	log "output file path is: " & _outputFilePath
	
end retrieveCommandLineArguments


(*
 processSpreadsheet

 This function is the workhorse of this script. We open Numbers,
 have it load the source spreadsheet, and invoke the export command
 to ultimately write the output CSV to the specified path.
*)
on processSpreadsheet()
	tell application "Numbers"
		activate
		
		-- Before we open the file asked of us, close out every document 
		-- that might have opened along with the application having activated:
		close every window saving no
		
		-- Retrieve information about the source file:
		set fileInfo to (info for (_inputFilePathAlias))
		set fileName to name of (fileInfo)
		set fileExtension to name extension of (fileInfo)
		log "Opening source document " & fileName & "..."
		
		tell (open _inputFilePathAlias)
			-- In this scope, we are now implicitly dealing with the document just opened
			-- as the current target, which means we access it through the "it" keyword,
			-- as per: https://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/AppleScriptLangGuide/conceptual/ASLR_fundamentals.html#//apple_ref/doc/uid/TP40000983-CH218-SW4
			
			set activeDocument to it
			
			-- Note: We could have also gotten to the active document by walking the chain from the top,
			-- i.e. right from the Application object:
			--set activeDocument to document 1 of application "Numbers" 
			
			say "Starting Export."
			with timeout of 600 seconds
				export activeDocument as CSV to file _outputFilePath
				-- Use this instead if you want to export to Excel:
				-- export activeDocument as Microsoft Excel to file _outputFilePath 
			end timeout
			say "Completed Export."
			
			-- Since we closed out other windows that might have been open before
			-- opening the file we sought, we really should only have one document
			-- window open.
			close activeDocument
		end tell
		
		quit
		
	end tell
end processSpreadsheet


(*
 ensureUTF8Encoding

 Microsoft Excel on the Mac is not good with exporting special
 characters as is Apple Numbers. Part of this is in the ability for
 Numbers to correctly process UTF8 formatting when exporting.

 Setup default export encoding for CSV files to UTF8, so without 
 specifying anything further for AppleScript, the right format will 
 be applied automatically. Since we cannot specify the CSV export
 encoding via AppleScript, we will set it via the Defaults Database
 with a shell command.
 
 Here are the codes that apply: 4=UTF8, 12=windows latin, 30=MacRoman
 As such, we'll specify 4 for UTF8.

 This technique courtesy of: https://discussions.apple.com/thread/4018778?tstart=0 
*)
on ensureUTF8Encoding()
	do shell script "/usr/bin/defaults write com.apple.iWork.Numbers CSVExportEncoding -int 4"
end ensureUTF8Encoding

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

#! /usr/bin/osascript

---------------------------------------------------------------------------------

Script: SpreadsheetExportToCSV

Command-line tool to convert a spreadsheet document to CSV

This AppleScript is tested with and compatible with Apple iWork Numbers 3.6,

current as at October 23, 2015.

Parameters:

1. Full Path to the input file, including file extension

2. Full Path to the output file, including file extension

Example command-line invocation:

osascript SpreadsheetExportToCSV.scpt "/Users/me/Documents/MySpreadsheet.xlsx" "/Users/me/Documents/Converted/OutputFile.csv"

The spreadsheet to use as an input file can be an Excel file or a Numbers file.

Sohail A.

Blog: http://sohail.io

Twitter: @idStar

Creation Date: October 23, 2015

---------------------------------------------------------------------------------

global _inputFilePathAlias

global _outputFilePath

global _requestedOptions

run

This is our entry point, our main function, where this script

begins execution. We call out to helper functions, to modularize

the design.

on run argv

-- Ensure our CSV files are encoding with UTF8:

ensureUTF8Encoding()

-- Parse and determine input/output paths:

retrieveCommandLineArguments(argv)

-- Perform the actual activation, file open, export and cleanup:

processSpreadsheet()

end run

---------------------- SUPPORTING FUNCTIONS --------------------------

retrieveCommandLineArguments

Handles parsing the command line arguments passed to us.

We return a list, where the first element is the input file

path as an alias. The second element is the output path,

as text (as it may not yet exist).

on retrieveCommandLineArguments(command_line_arguments)

set _inputFilePathAlias to POSIX file (item 1 of command_line_arguments) as alias

set _outputFilePath to (POSIX file (item 2 of command_line_arguments)) as text

log "input file path is: " & _inputFilePathAlias

log "output file path is: " & _outputFilePath

end retrieveCommandLineArguments

processSpreadsheet

This function is the workhorse of this script. We open Numbers,

have it load the source spreadsheet, and invoke the export command

to ultimately write the output CSV to the specified path.

on processSpreadsheet()

tell application "Numbers"

activate

-- Before we open the file asked of us, close out every document

-- that might have opened along with the application having activated:

close every window saving no

-- Retrieve information about the source file:

set fileInfo to (info for (_inputFilePathAlias))

set fileName to name of (fileInfo)

set fileExtension to name extension of (fileInfo)

log "Opening source document " & fileName & "..."

tell (open _inputFilePathAlias)

-- In this scope, we are now implicitly dealing with the document just opened

-- as the current target, which means we access it through the "it" keyword,

-- as per: https://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/AppleScriptLangGuide/conceptual/ASLR_fundamentals.html#//apple_ref/doc/uid/TP40000983-CH218-SW4

set activeDocument to it

-- Note: We could have also gotten to the active document by walking the chain from the top,

-- i.e. right from the Application object:

--set activeDocument to document 1 of application "Numbers"

say "Starting Export."

with timeout of 600 seconds

export activeDocument as CSV to file _outputFilePath

-- Use this instead if you want to export to Excel:

-- export activeDocument as Microsoft Excel to file _outputFilePath

end timeout

say "Completed Export."

-- Since we closed out other windows that might have been open before

-- opening the file we sought, we really should only have one document

-- window open.

close activeDocument

end tell

quit

end tell

end processSpreadsheet

ensureUTF8Encoding

Microsoft Excel on the Mac is not good with exporting special

characters as is Apple Numbers. Part of this is in the ability for

Numbers to correctly process UTF8 formatting when exporting.

Setup default export encoding for CSV files to UTF8, so without

specifying anything further for AppleScript, the right format will

be applied automatically. Since we cannot specify the CSV export

encoding via AppleScript, we will set it via the Defaults Database

with a shell command.

Here are the codes that apply: 4=UTF8, 12=windows latin, 30=MacRoman

As such, we'll specify 4 for UTF8.

This technique courtesy of: https://discussions.apple.com/thread/4018778?tstart=0

on ensureUTF8Encoding()

do shell script "/usr/bin/defaults write com.apple.iWork.Numbers CSVExportEncoding -int 4"

end ensureUTF8Encoding

Verifying the Status of an Auto-Renewable Subscription

October 19, 2015

There’s a growing trend of iOS apps offering auto-renewable subscriptions as their mode of generating In-App Purchase (IAP) revenue. I’ve recently implemented Auto-Renewable Subscriptions in a client’s universal iOS app, and have some lessons learned that I’d like to share in this post.

If you’re not familiar with Store Kit for In-App Purchases, Auto-Renewable Subscriptions or Receipt Validation, you’ll want to start with the Resources section at the end of this post.

During development of subscription functionality, I had recently run into an ambiguous situation regarding receipt validation. The problem encountered was fortunately caught in the early days of a soft launch.

In this post, my aim is to provide you with more clarity on receipt versioning than I had at the outset. Here, I’ll cover what to do when an app with an active subscription is updated from the App Store to a new version that you’ve released, and how to determine whether users have a subscription to your app using only the App Receipt.

The Lesson

There’s a lot of stock advice and sample code regarding receipt validation, but there’s a risk of putting it together wrong.

For example, if you’re dealing exclusively with iOS7 and later, an Auto-Renewable Subscription purchase can always be found in a valid and current App Receipt. But if you check the app’s bundle version against that in the App Receipt every time you need to verify subscription status, your subscribers will be locked out when they install a new app update that you’ve released.

This is because from my experience, the bundle version stored within an App Receipt is not updated to match the version of every app update that your users will install. Rather, the App Receipt retains the bundle version of the last app release through which the user most recently conducted a purchase.

I’ve not seen any tutorials or WWDC videos speak to under what conditions the version number in the receipt gets revised. Have you? If so, I’d love you to point fellow readers and myself to such material. Leave a comment or message me on Twitter.

Examples in written guides and videos seem to always talk about receipt validation in the context of having just made a purchase, where the bundle version of the app that just made the purchase and the bundle version retrieved from the receipt, will match up. Historically, developers have had to verify receipts and then store IAP information themselves.

If however, you are verifying the receipt as part of your mechanism to determine subscription status whenever your app launches because you don’t store that information anywhere else, then you should not insist on the bundle version matching as part of your receipt validation process.

Prevailing Examples

Whenever I’d see a tutorial on receipt validation, I’d almost always see the following three things being checked after the receipt is located and confirmed to be properly signed by Apple:

The bundle identifier
The bundle short version string
The hash of the GUID

The Receipt Validation Programming Guide

Here’s the Validate the Receipt section of Apple’s official Receipt Validation Programming Guide:

To validate the receipt, perform the following tests, in order:

Locate the receipt. If no receipt is present, validation fails.
Verify that the receipt is properly signed by Apple. If it is not signed by Apple, validation fails.
Verify that the bundle identifier in the receipt matches a hard-coded constant containing the CFBundleIdentifier value you expect in the Info.plist file. If they do not match, validation fails.
Verify that the version identifier string in the receipt matches a hard-coded constant containing the CFBundleShortVersionString value you expect in the Info.plist file. If they do not match, validation fails.
Compute the hash of the GUID as described in Compute the Hash of the GUID. If the result does not match the hash in the receipt, validation fails.

If all of the tests pass, validation passes.

This makes sense in that when a user makes a purchase, such as a subscription, the bundle version in the receipt that’s implicitly refreshed as part of the purchase will match the bundle version of the app currently running. IAP tutorials will often store purchase history in NSUserDefaults, to keep things simple. They also advise you to consider something more robust as a method of recording keeping.

You need to take security measures appropriate with the value of your IAPs, the user demographic you anticipate and the attack vectors you are keen on providing a defense against.

WWDC Sessions

In Session 308 at WWDC 2013 entitled Using Receipts to Protect Your Digital Sales, you can find advice to check the bundle version as part of your verification process. See the segment at time index [25:30] to about [27:00]. We’re also advised at time index [37:00] through [38:00] that Auto-Renewable Subscriptions are always in the App Receipt. And further, that you could restore transactions to verify the purchase.

What’s not covered is what exactly you would check in the App Receipt when attempting to verify a purchase. To me, if you check the bundle identifier and the hash of the GUID, leaving out the bundle version, you have verified the purchase. You can still check subscription expiry information without having to issue a restore transactions request.

To add another layer of security, you could store subscription status with the user’s counterpart account in the cloud, if your app extends beyond just the mobile device. However, if you only have an app where everything takes place on the device, I have found that relying on the App Receipt itself as the source of truth for Auto-Renewable Subscription status, works very well.

Oliver Drobnik’s Talk

The clearest information on this I’ve seen anyone call out, comes from the wise Oliver Drobnik who in his presentation iOS Subscriptions 2.0, provides guidance on this topic at time index [31:37] to [36:00]. Oliver specifically calls out that bundle versions may not match up, and so we wouldn’t always insist that they do when performing receipt validation in all cases.

Ideas for Improvement

What I’d like to see from Apple at a WWDC session, is explicit clarification on when the App Receipt bundle version is revved. I’ve since inferred this on my own through development, trial and error. However, I believe it’s worth an explicit mention.

At the outset, I had naively assumed that if the user had updated an app from the App Store, that their App Receipt would also get updated to reflect the current bundle version of the app build that they were now running.

If I were on the Store Kit team, I would have the App Receipt bundle version revved with every app update that the user downloaded, and store within the individual purchase entries, the bundle version associated with what was running on the user’s device at the time they had made that purchase. Regardless, this is all workable as currently designed once we understand the specifics about bundle version revisions in the App Receipt.

Auto-Renewable Subscription Stored in App Receipt

Assuming you’re dealing with a modern iOS app (iOS7 and later), you don’t actually need to store the user’s subscription status anywhere; you can simply retrieve it from the App Receipt. Auto-Renewable Subscription purchases are always present in the App Receipt.

Of course, if the user first purchased the app’s subscription on their iPhone and then later downloaded a copy on their iPad, you would need to provide the user with a Restore Subscription button somewhere, where your task would be to refresh the App Receipt. Once you do that however, you have your source of truth with you always, for determining subscription status.

Let’s say that on each app launch, you set a Boolean property somewhere that indicates whether the user has an active subscription or not. You can lazily set this property by checking the App Receipt.

Here’s what you do not want to do: Trigger a receipt refresh request each time the app launches or worse, every time your app checks whether the user has an active subscription in order to enable some content or functionality.

Once each device has an App Receipt that indicates the presence of the active subscription, you don’t need an Internet connection nor do you need to refresh the receipt each time you wish to check their subscription status. Instead, you follow the steps outlined above from the Receipt Validation Programming Guide, skipping Step 4.

The only time you want to include Step 4, is when you are validating the receipt right after having made a purchase. It’s only at this time that the version in the App Receipt and the version of your app, would be expected to match up.

Scenario 1: Verifying a Purchase Just Made

When your app successfully completes the purchase or renewal of a subscription, Store Kit will no doubt inform you by invoking the method:

- (void)paymentQueue:(SKPaymentQueue *)queue updatedTransactions:(NSArray *)transactions

on your class that implements the SKPaymentTransactionObserver protocol. This is the time however, to actually look at the App Receipt that would have been updated on your user’s device, to verify that the App Receipt is valid, and that it actually contains the purchase just made. In this case, the Auto-Renewable Subscription.

It is in this scenario that you are going to include verifying the bundle version as part of the receipt verification process.

Verifying a purchase just made

What you do not want to do here, is use the above flow to attempt to restore an active subscription, possibly purchased on another device. If you do that, you risk failing receipt verification because the bundle version of the receipt you refresh and the bundle version of the app currently running, may not match up.

Scenario 2: Checking Subscription Status

In this scenario, the user is not asking to purchase or renew a subscription. They are merely trying to access a part of your app that would require an active subscription, or you are checking to see if you should display some content that would only present, if the user had an active subscription.

At this juncture, what is important is that:

You do not trigger a dialog requesting the user do something if they didn’t expressly interact with your UI. That means that you do not trigger a receipt refresh request unless the user did something explicit, like tap a ‘Restore Subscription’ button.
If the user has a subscription, you should not need an Internet connection to verify this when you look to the purchase information found within the local App Receipt.

As you’ll see below, an app that follows this path for periodic subscription status checks can verify and parse the App Receipt almost instantly, without needing the Internet or pestering the user.

In this way, you do not need to have any additional system of record for a user’s Auto-Renewable Subscription status — you can retrieve it from the App Receipt whenever your app needs to know.

Checking subscription status

It’s at this juncture, that if the user attempts to do something for which they are missing a subscription, that you can offer to sell them an auto-renewing subscription. Additionally, you can offer to check if they have an active subscription that can be restored onto the current device. The later option is where you issue a refresh receipt request from the App Store, which will automatically trigger an App Store credentials request of the user.

Resources

Here are the promised resources mentioned at the start of this post.

Background

Note that only a fraction of apps are currently able to take advantage of auto-renewable subscriptions, given Apple’s App Store guidelines.1 However, there does seem to be an easing over the last few years of what qualifies.

With an Auto-Renewable Subscription, users are paying for ongoing new content or a service. Content may be episodic like a magazine, a database of media that can be streamed on demand or a constantly updated medical reference database. In the service category, think of apps like Evernote that allow subscribers to sync data with Evernote client apps on other platforms.

Videos and Tutorials

This post did not provide an introduction to IAP, Auto-Renewable subscriptions or Receipt Validation. For that, I’d suggest the following resources:

Video: iOS Subscriptions 2.0. This is a presentation that Oliver Drobnik of Cocoanetics gave in October 2015. A great introduction and overall summary.
Video: Managing Subscriptions with In-App Purchase. This is Session 308 from WWDC 2012. Explains the mechanics of subscriptions and their timelines.
Video: Using Receipts to Protect Your Digital Sales. This is Session 308 from WWDC 2013. Covers the high level process of validating receipts on your device.
Article: Receipt Validation. This is from Issue 17 of the highly acclaimed objc.io online publication.
Guide: In App Purchase Programming. This is an official guide from Apple.

To learn the basics of Store Kit and obtain a gentle introduction to receipt validation, I’d recommend Chapter 9 (“Beginning In-App Purchases”) of iOS 6 by Tutorials,2 from the Ray Wenderlich tutorial team.

Section 11.15 states: Apps may only use auto-renewing subscriptions for periodicals (newspapers, magazines), business Apps (enterprise, productivity, professional creative, cloud storage), and media Apps (video, audio, voice), or the App will be rejected [↩]
Don’t be fooled by the “iOS6” in the title. The book has been updated to reflect changes in any related API inclusive of iOS8, and all of that works just fine for StoreKit with iOS9. The iOS version number in the book title reflects the iOS version the contained topics were first introduced by Apple, or for when said topics underwent major changes [↩]