sohail.io

  • About

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
 

Filed Under: General Tagged With: AppleScript, csv, Excel, gist, iWork, Numbers

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:

  1. The bundle identifier
  2. The bundle short version string
  3. 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:

  1. Locate the receipt. If no receipt is present, validation fails.
  2. Verify that the receipt is properly signed by Apple. If it is not signed by Apple, validation fails.
  3. 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.
  4. 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.
  5. 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:

  1. 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.
  2. 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:

  1. Video: iOS Subscriptions 2.0. This is a presentation that Oliver Drobnik of Cocoanetics gave in October 2015. A great introduction and overall summary.
  2. Video: Managing Subscriptions with In-App Purchase. This is Session 308 from WWDC 2012. Explains the mechanics of subscriptions and their timelines.
  3. 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.
  4. Article: Receipt Validation. This is from Issue 17 of the highly acclaimed objc.io online publication.
  5. 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.

  1. 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 [↩]
  2. 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 [↩]

Filed Under: iOS Tagged With: auto-renewable, bundle version, receipt, refresh, subscriptions, validation

search

Categories

  • General
  • iOS

Recent Posts

  • AppleScript Export to CSV via iWork Numbers
  • Verifying the Status of an Auto-Renewable Subscription
  • Swift Memory Management Exercise
  • Learning Swift: My Approach
  • UIAutomation Command Line Test Runner for Xcode 6

This Blog

My name is Sohail. I'm a developer-consultant and entrepreneur primarily focused on building high quality iOS apps.

I write about software development. Mostly iOS related and sometimes, Ruby on Rails.

See the About page for more bio.

Sohail Ahmed - About page for blog author bio

Category Specific RSS

  • General (2)
  • iOS (6)

Archives

  • October 2015 (2)
  • June 2015 (2)
  • September 2014 (1)
  • October 2013 (3)

Copyright © 2021 Sohail A. · Log in

All opinions expressed are strictly those of the author, and do not necessarily reflect those of guests, partners, sponsors, customers or affiliates.