I Sheet You Not: Plug Excel into Alfred

[deanishe]

I Sheet You Not

 

Create auto-updating workflows from Excel worksheets. Works like a List Filter but using an Excel file as the data source.

 

 

I Sheet You Not is a workflow generator/template for Alfred 3. It reads data from an Excel workbook and displays them in Alfred. You can specify which rows and columns the data are read from, and changes to the data are picked up automatically by the workflow.

 

Download and installation

 

Download the workflow from Packal or  GitHub releases and double-click the downloaded I-Sheet-You-Not-X.X.X.alfredworkflow file to install in Alfred.

 

Usage

 

Use keyword isyn to create a new copy of the workflow. You can either create an empty copy or search for an Excel file to base the workflow on.

 

See the documentation for detailed usage/configuration instructions.

 

Source code

 

The source is hosted on GitHub.

 

Bugs and feature requests

 

Bugs and feature requests should ideally be submitted via GitHub issues, but asking in this thread is cool, too.

Edited November 2, 2016 by deanishe
Add Packal link

[MuppetGate]

This has got to be the best workflow name ever

[deanishe]

I'm thinking that in hindsight "Full of Sheet" might have been better…

 

I've been having a really hard time figuring out a good way to describe clearly and briefly what the workflow does.

[Maddog]

Tried to create a new excel workflow, I typed isyn and it found my excel file in the documents folder, I gave it a name, then this error came up:

[ERROR: action.script] Building workflow "tasks" in /Users/user/Library/Caches/com.runningwithcrayons.Alfred-3/Workflow Data/net.deanishe.alfred-i-sheet-you-not ...
building workflow 'tasks' version 0.1.2 ...
Copying workflow contents to build/ ...
Cleaning info.plist ...
Set bundle ID: net.deanishe.alfred-i-sheet-you-not.pz7D0vGFosaiTVlXrzGDLB0P3XGNSMcSut8FfS0n
Set workflow name: tasks
Set DOC_PATH: /Users/user/Documents/my-excel.xlsx
Delete: Entry, ":variables:DEV", Does Not Exist
Deleting build directory ...
Error updating info.plist

[deanishe]

My bad. Setting DEV to 1 in the workflow variables configuration sheet in your main copy should fix that.

I'll try to fix it properly and make a new release tomorrow.

[deanishe]

Updated today (v0.2.1). Fixed the above bug and cleaned up workflow generation.

 

Took a lot longer to fix, as I rewrote quite a lot of the generation code. Now, when you generate a new workflow, the red generation elements will be removed from it.

[katie]

I think this is a really cool and useful idea for a workflow. I was trying to check out one of the demo's (Random Names) but I get a "fatal error". Screenshot attached.

 

Here's what came out of the debugging mode:

Starting debug for 'I Sheet You Not'

[2016-11-01 14:15:24][STDERR: input.scriptfilter] options=Namespace(docpath='./Demo.xlsx', sheet='1', start_row='1', subtitle_col='', title_col='1', value_col='', variables={})
------ alfred env vars -------
debug='1'
preferences='/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences'
preferences_localhash='e4d7f5380faa43a368549ac955f682f95dfe7d0a'
theme='alfred.theme.custom.FDFFD75C-5F96-4977-AB98-9BC379B75E35'
theme_background='rgba(255,255,255,0.89)'
theme_selection_background='rgba(27,187,155,1.00)'
theme_subtext='0'
version='3.1.1'
version_build='737'
workflow_bundleid='net.deanishe.alfred-i-sheet-you-not'
workflow_cache='/Users/katie/Library/Caches/com.runningwithcrayons.Alfred-3/Workflow Data/net.deanishe.alfred-i-sheet-you-not'
workflow_data='/Users/katie/Library/Application Support/Alfred 3/Workflow Data/net.deanishe.alfred-i-sheet-you-not'
workflow_name='I Sheet You Not'
workflow_uid='user.workflow.252BBBAD-F952-4D6F-A942-3FD6F353E5D2'
workflow_version='0.2.1'
------------------------------
Changing bundle ID to u'aebeea3b23064f60834136b6c503292b' ...
cmd=[u'/usr/libexec/PlistBuddy', u'-c', u'Set :bundleid aebeea3b23064f60834136b6c503292b', u'/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.252BBBAD-F952-4D6F-A942-3FD6F353E5D2/isheetyounot/info.plist']
Set: Entry, ":bundleid", Does Not Exist
################ FATAL ERROR ##################
Traceback (most recent call last):
  File "/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.252BBBAD-F952-4D6F-A942-3FD6F353E5D2/isheetyounot/aw3.py", line 199, in rescue
    fn()
  File "/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.252BBBAD-F952-4D6F-A942-3FD6F353E5D2/isheetyounot/cli.py", line 149, in main
    change_bundle_id(newid)
  File "/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.252BBBAD-F952-4D6F-A942-3FD6F353E5D2/isheetyounot/aw3.py", line 252, in change_bundle_id
    check_call(cmd)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/subprocess.py", line 540, in check_call
    raise CalledProcessError(retcode, cmd)
CalledProcessError: Command '[u'/usr/libexec/PlistBuddy', u'-c', u'Set :bundleid aebeea3b23064f60834136b6c503292b', u'/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.252BBBAD-F952-4D6F-A942-3FD6F353E5D2/isheetyounot/info.plist']' returned non-zero exit status 1
################# END ERROR ###################
--------------- 0.025s elapsed ---------------

I'm on Mac OS El Capitan (10.11.16), with the latest versions of Alfred 3 (3.1.1 build 736) and this workflow (downloaded today).

[deanishe]

Thanks for spotting that. Fixed in v0.2.2.

 

I missed it because I develop using a "special" version rigged to not automatically change its bundle ID (which is where it failed). A "normal" version has to change its own bundle ID, so you can install multiple copies in Alfred.

[katie]

Thank you for fixing it! I was able to check out the examples and they are very cool!

 

I put together a list of Cree phrases, just to try it out. The Excel file simply has Cree in column A and English in column B. I was able to create the new workflow, but got an error -  screenshot attached.

 

Here's the output from debugging mode:

[2016-11-01 18:15:32][STDERR: input.scriptfilter] options=Namespace(docpath='/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Cree Phrases.xlsx', sheet='1', start_row='1', subtitle_col='', title_col='1', value_col='', variables={})
------ alfred env vars -------
debug='1'
preferences='/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences'
preferences_localhash='e4d7f5380faa43a368549ac955f682f95dfe7d0a'
theme='alfred.theme.custom.FDFFD75C-5F96-4977-AB98-9BC379B75E35'
theme_background='rgba(255,255,255,0.89)'
theme_selection_background='rgba(27,187,155,1.00)'
theme_subtext='0'
version='3.1.1'
version_build='737'
workflow_bundleid='net.deanishe.alfred-i-sheet-you-not.te2WoXM9HJuOzaOHoNuFrhufwUBodNEO3mCu8n8d'
workflow_cache='/Users/katie/Library/Caches/com.runningwithcrayons.Alfred-3/Workflow Data/net.deanishe.alfred-i-sheet-you-not.te2WoXM9HJuOzaOHoNuFrhufwUBodNEO3mCu8n8d'
workflow_data='/Users/katie/Library/Application Support/Alfred 3/Workflow Data/net.deanishe.alfred-i-sheet-you-not.te2WoXM9HJuOzaOHoNuFrhufwUBodNEO3mCu8n8d'
workflow_name='Cree Phrases'
workflow_uid='user.workflow.24BE8C94-A724-44D6-8742-3E5CB2A46DC2'
workflow_version='0.2.1'
------------------------------
doc_age=4h4m
cache_path=u'~/Library/Caches/com.runningwithcrayons.Alfred-3/Workflow Data/net.deanishe.alfred-i-sheet-you-not.te2WoXM9HJuOzaOHoNuFrhufwUBodNEO3mCu8n8d/a22/ef7/64e/a22ef764e2480554ab32af14a6e30ae3.json'
sheet='1', start_row=1, cols=[1, 2, 3], vars={}
Opened worksheet "Sheet1" of ~/Dropbox/Apps/Aflred/Alfred 3/Cree Phrases.xlsx
################ FATAL ERROR ##################
Traceback (most recent call last):
  File "/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.24BE8C94-A724-44D6-8742-3E5CB2A46DC2/isheetyounot/aw3.py", line 239, in rescue
    fn()
  File "/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.24BE8C94-A724-44D6-8742-3E5CB2A46DC2/isheetyounot/cli.py", line 188, in main
    items = read_data(o.docpath, o.sheet, cols, start_row, o.variables)
  File "/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.24BE8C94-A724-44D6-8742-3E5CB2A46DC2/isheetyounot/core.py", line 248, in read_data
    arg = s.cell(i, cols[2]).value
  File "/Users/katie/Dropbox/Apps/Aflred/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.24BE8C94-A724-44D6-8742-3E5CB2A46DC2/xlrd/sheet.py", line 399, in cell
    self._cell_types[rowx][colx],
IndexError: array index out of range
################# END ERROR ###################
--------------- 0.032s elapsed ---------------

 

[deanishe]

Try setting VALUE_COL to 0 in the configuration sheet (or passing the option --value 0 to the isyn command).

Edited November 1, 2016 by deanishe

[katie]

That fixes it! Sorry - I must have misunderstood that yellow "Important" box in the documentation.

 

Thank you for your time and help!

[deanishe]

I updated the "important" box right after your previous post to make it clearer 

 

[katie]

Ah okay, I was wondering how I missed something like that

[deanishe]

Oh, the information was there before, but only in passing. 

 

Your comment made it clear that I should have written a big, scary warning. So I added that.

 

After all, what's the point of writing documentation if the important stuff isn't obvious?

[katie]

I will admit to scanning documentation and being herded effectively by big scary warnings  

[xilopaint]

Thank you for this template, @deanishe! I can't believe that I took so long to discover this.

 

Let me do a question. I want to make a workflow as following:

 

1) the user selects a city;

 

2) the user selects a court from that city;

 

3) information (title and subtitle) of the selected court is displayed (basically the judge name and the court telephone).

 

How can I get Alfred to continue filtering after the user hits enter on the selected city?

 

Maybe this is more a question about script filters in general than this template itself but until the moment I didn't need to create a workflow that works in this way.

[deanishe]

Use the output from your first filter (city) to dynamically set the filename of the file containing the list of courts in that city, and plug that into the second filter (court).

 

If you don't know how to do that, you should ask in the Workflow Help & Questions forum, not in this thread: as you say, it's not a question about this workflow.

[xilopaint]

1 hour ago, deanishe said:

Use the output from your first filter (city) to dynamically set the filename of the file containing the list of courts in that city, and plug that into the second filter (court).

 

And it's not possible to have all data in a single Excel workbook even if I have to use more than one filter?

 

1 hour ago, deanishe said:

If you don't know how to do that, you should ask in the Workflow Help & Questions forum, not in this thread: as you say, it's not a question about this workflow.

 

Could you move my post to a new thread? I am a bit lost because I want to use this template to create a workflow, so I don't know if my questions are totally unrelated to this thread.

Edited April 20, 2017 by xilopaint

[deanishe]

The Script Filters this workflow provides work just the same as any other, and you can connect them together like any others.

 

50 minutes ago, xilopaint said:

And it's not possible to have all data in a single Excel workbook even if I have to use more than one filter?

 

The other way around. It's using multiple filters that makes it difficult to keep all the data in one file because each filter needs a different dataset. You'd probably have to create a separate worksheet for each city.

 

50 minutes ago, xilopaint said:

Could you move my post to a new thread?

 

No. I can't create threads in your name.

 

50 minutes ago, xilopaint said:

I don't know if my questions are totally unrelated to this thread.

 

We're talking about how to chain multiple Script Filters, which is a feature of Alfred, not ISYN. ISYN is basically just a fancy List Filter that pulls its data from an Excel file.

 

Edited April 20, 2017 by deanishe

[xilopaint]

I got my workflow to work with chained Script Filters. Thank you!

 

So I have one more question: is there any way to make ISYN filter the content of an individual row?

 

It's important for the logic of my workflow. I don't want to create a sheet for every single court with the judge name and phone number. It would be massive work.

Edited April 20, 2017 by xilopaint

[deanishe]

2 hours ago, xilopaint said:

So I have one more question: is there any way to make ISYN filter the content of an individual row?

 

No, afraid not.

 

If I were you, I'd either change the workflow logic to fit the data or I'd use the xlrd library the workflow is based on to export the data to a format that better fits the logic.

 

[xilopaint]

2 hours ago, deanishe said:

 

No, afraid not.

 

If I were you, I'd either change the workflow logic to fit the data or I'd use the xlrd library the workflow is based on to export the data to a format that better fits the logic.

 

 

Unfortunately it's not possible to change the logic to fit all the data unless I join the judge name and the phone number on a subtitle column so that I would have two script filters concatenated instead of three.

 

My original plan was:

 

Script Filter 1

– Title: city

– Subtitle: none

 

Script Filter 2

– Title: court

– Subtitle: none

 

Script Filter 3

– Title: judge name

– Subtitle: court phone

 

It would be great if ISYN had an optional argument in its command-line to set a row number to be single filtered by the workflow. It would make data creation much simpler on user side in many cases. Please, if possible consider this a feature request.

 

EDIT: I just realized that I can use an action modifier in order to have only two scripts filters. It seems a good solution. Let me try.

Edited April 20, 2017 by xilopaint

[deanishe]

16 minutes ago, xilopaint said:

It would be great if ISYN had an optional argument in its command-line to set a row number to be single filtered by the workflow. It would make data creation much simpler on user side in many cases. Please, if possible consider this a feature request.

 

Fundamentally, I am not going to change the one-row-per-item model. People do crazy things with Excel, and I'm not going to go down the rabbit hole of fielding a feature request every time somebody has some data that don't fit that model.

 

If you actually showed me the data you're talking about, I might have an idea.

[cands]

Just wanted to say thanks (and I bought you a beer) for this powerful and simple-to-use workflow! I recently needed to send a large amount of emails based on an Excel-sheet with relevant information in several columns. It was pretty straightforward to extract the needed data with your workflow and create the text for each email. 

[deanishe]

On 6/4/2018 at 1:22 PM, cands said:

Just wanted to say thanks (and I bought you a beer) for this powerful and simple-to-use workflow!

 

? Beer and thanks are always appreciated.