I Sheet You Not: Plug Excel into Alfred

[joeynotjoe]

Hello! This workflow is absolutely ridiculous, unbelievably useful- so thank you once again, @deanishe! It almost made me throw my keyboard out the window when I realized how easy and powerful this could be.

 

So after diving in, I have two questions:

 

1. Is it possible to use multiple columns in one of the fields (title, subtitle)? The motivation here is to quickly see more info from the spreadsheet, but also to not have to 'fix it' with formulas on the spreadsheet itself.
    
2. Is there a good way to set the MATCH variable to only match if it is an exact result? I only need this for instances where I'm passing a number to the spreadsheet, but if I pass '1' to it, it's currently finding 12, 133, etc. I tried looking up the python formatting that you link to in your documentation, but it's all a bit over my head.

Thanks so much!

 

[deanishe]

6 hours ago, joeynotjoe said:

Is it possible to use multiple columns in one of the fields (title, subtitle)?

 

No, afraid not.

 

6 hours ago, joeynotjoe said:

Is there a good way to set the MATCH variable to only match if it is an exact result?

 

Also no, I’m afraid. Alfred is filtering the results, not the workflow. It doesn’t know anything about numbers, so the query “1” means “words starting with the character ‘1’”.

 

[Flow]

Hi !

I am very very interested in this tool as I am a heavy Excel user, and I believe it would make me gain a lot of time.
I cannot get it to work with Alfred 4 though. I read through this thread quickly but I don't think this has been discussed.

Is this workflow compatible with Alfred 4 ?

 

I cannot populate the fields "Date_format" / "Doc_path" ... as whenever I write something in there it goes blank as soon as I press enter.

Also, when I try to create a workflow with iSyn, whenever I select an excel file it does not create the workflow.

 

Any help is appreciated

Thanks !

[deanishe]

I haven't really tested this workflow with Alfred 4, but I believe it should be compatible.

 

1 hour ago, Flow said:

I cannot populate the fields "Date_format" / "Doc_path" ... as whenever I write something in there it goes blank as soon as I press enter.

 

That should definitely not happen. It sounds like you may have a permissions issue, i.e. Alfred can't write to the workflow's info.plist.

 

Which version of macOS are you running? Have you tried deleting and re-installing the workflow?

[Flow]

I am running MacOS 10.12.6 (Sierra)

Alfred 4 fully up to date. Should I revert to Alfred 3 maybe ?

In other workflows I have the same issue of not being able to write in those fields.

I have tried reinstalling the workflow multiple times, yes.

If I can give you any more information, please tell me.

 

Alfred is authorised to modify stuff on my computer :

 

[deanishe]

7 minutes ago, Flow said:

I have tried reinstalling the workflow multiple times, yes.

 

And what happened?

 

Like I said, it sounds like you have a permissions issue. That means the files are not writable. It is not the same as the Security & Privacy settings you're looking at.

[Flow]

I never could write anything in there

Even after reinstalling the workflow

[Flow]

Just installed Alfred 3, and it works..

[deanishe]

13 minutes ago, Flow said:

Just installed Alfred 3, and it works..

 

Alfred 3 uses different directories, and you appear to have incorrect permissions on your Alfred 4 directories.

[Bernardo_V]

Incredibly useful workflow. Thank you for it, @deanishe.

 

I was wondering if it is possible to set up a match column on the sheet or if the workflow will always match the title only.

[deanishe]

2 hours ago, Bernardo_V said:

I was wondering if it is possible to set up a match column on the sheet or if the workflow will always match the title only.

 

You can't directly specify a particular column, no. ISYN supports setting a match field, but it uses interpolation of extracted variables, rather than a specific column, as you might will often want to match across multiple columns without having to alter the spreadsheet.

 

Matching is fairly well documented, and the included isyncdemo quakes Script Filter shows it in action.

[dfay]

I happened to be working on extracting some data from an .xlsx file earlier and ended up using python & openpyxl - just wondering why you used xlrd instead @deanishe.  I'm sure you have a good reason & I know you know python way better than me....

[deanishe]

4 minutes ago, dfay said:

just wondering why you used xlrd instead

 

I don't remember, tbh. Looking at the openpyxl description, it was likely because xlrd also supports .XLS files, not just .XLSX.

[AuspiringMind]

Hello, I hope I'm not missing this elsewhere—I've read through the documentation, but I'm not having success with exporting a date format. I've included the DATE_FORMAT in the environment variables, and even tried additionally including it in the Script area of the filter I'm using the date for:

Environment Variables:

In the specific script filter

 

I've tried having the date column in the workbook set as a Date in the YYYY-MM-DD as well as having it set as "general" non-specified.

 

Regardless, I have the same result where what is copied to the clipboard is not formatted. Example:

 

Date Published: 2014-09-24

When copied through the workflow, it goes to the clipboard as: 41906

 

I'd appreciate any help getting the YYYY-MM-DD to copy properly. Thank you!

 

If it's needed:

I have Alfred 4.0.4

Running MacOS High Sierra 10.13.6 (17G8030)

 

[deanishe]

I can't tell you why an Excel file I've never seen isn't working.

[AuspiringMind]

On 10/16/2019 at 3:32 PM, deanishe said:

I can't tell you why an Excel file I've never seen isn't working.

Sorry, @deanishe  I didn’t know you'd want the file. It doesn't let me attach it in here,  but you can find it at this dropbox link.

 

Please let me know if there's more info I can provide. Thank you!

[deanishe]

Sorry. No idea. It works correctly for me. Please make sure you're using the latest version of the workflow.

 

On 10/16/2019 at 10:18 PM, AuspiringMind said:

it goes to the clipboard as: 41906

 

FWIW, this is how Excel stores dates internally. It's the number of days since 1 January 1900.

[AuspiringMind]

@deanishe I do have the most recent version of the workflow. When you tried it out, did you use the DATE_FORMAT environment variable or the export_FMT?

 

And for future reference, how would I update workflows I made through ISYN I  with new versions? Downloading a new version of the workflow will update the ISYN workflow itself, but does that affect any I've created with the red generating aspect prior to updates?

Edited October 18, 2019 by AuspiringMind

[deanishe]

1 hour ago, AuspiringMind said:

did you use the DATE_FORMAT environment variable or the export_FMT?

 

I tested both, and both work.

 

1 hour ago, AuspiringMind said:

how would I update workflows I made through ISYN I  with new versions?

 

You can't do it automatically. You'd copy all the files except info.plist over from a new copy of ISYN. It's not something you really need to do, though: the workflow is updated very rarely.

[AuspiringMind]

Thank you @deanishe. I downloaded the workflow again and replaced the files as you'd described, and now it's working. No idea what was wrong in the first place, but it's working now. :)  Thank you for your time.

[deanishe]

Grand. Glad to hear it.

[xgo]

I spent about 2 days a few months back creating effectively a (crappier) version of this to search databases of my company's part numbers and attributes, as that saves me >1min every time I need to find anything. That was before discovering this workflow, which could have saved me so much time! 

 

One note: the Packal version is not the latest (0.2.2 vs. GitHub's 0.3.2). This confused me when I was reading this thread about 'MATCH' after downloading the workflow from Packal. 

 

One question, and I will look into it and try to answer it myself. The search/matching doesn't work on words that are following a "-". For example, if I search for 'Columbia' (edit) 'Colombia' in the earthquake demo, the workflow doesn't return the "1906 Ecuador–Colombia earthquake" item. Only if I search for Ecuador will it show up.

 

I think this has to do with the MATCH format, I'll read into it and edit my post if I find the answer

edit: it's not just dash, it's basically any matching that isn't from the start of the word. The workflow I initially leveraged to create my custom solution was deanishe's Excel Demo, which leverages fuzzy matching and that just works out of the box. I am not sure if there's an easy way to set up that kind of midstring matching with the format. My usecase would require e.g. for me to type "cuad" and Alfred to return "1906 Ecuador–Colombia earthquake"

Edited July 19, 2020 by xgo
fuzzy matching

[dfay]

23 minutes ago, xgo said:

if I search for 'Columbia' in the earthquake demo, the workflow doesn't return the "1906 Ecuador–Colombia earthquake" i

ummm it might be the spelling of your query term...

[xgo]

38 minutes ago, dfay said:

ummm it might be the spelling of your query term...

see my post edit; appears we basically posted at the same time. I think what I'm looking for is 'mid-string' matching. E.g. "Colombia", "lomb", "Ec", "uador" would all match to "1906 Ecuador–Colombia earthquake"

 

edit: ahh Colombia, yes.  same issue!

Edited July 19, 2020 by xgo

[deanishe]

He's pointing out that "Columbia" (the US district) and "Colombia" (the country) are not the same.

 

Alfred is doing the matching, not the workflow. If you want fuzzy search, you can add this fuzzy-search script to your workflow.