4 ms·

I thought I would disagree, but I very much agree that we need something like zapier for custom internal processes. I’ve automated thousands of hours worth of t

by soared 5y ago

I thought I would disagree, but I very much agree that we need something like zapier for custom internal processes. I’ve automated thousands of hours worth of things with zapier and gsheets. But if there isn’t a zap for it, and you can’t hack your way around it, nontechnical users are generally out of luck. I would love for gsheets to be able to extend further into the browser and my desktop, because there is so much value and potential there. For example, a problem I worked on yesterday: I receive an email every day that has an attachment with the .eml file type. In that attachment is a link to a csv. I need the data from the csv in excel or sheets for daily use. There are no zaps to convert a .eml to any filetype - so I had to automate the attachment downloading to gdrive on my desktop, have a local automation run to convert .eml to .txt, then back in zapier grab the url from the .txt and deliver it to gsheets. Then the gsheet can just grab the data from the csv url. Problem is - I couldn’t figure out how to automate the .eml conversion, so I’m stuck!

mynameismon 5y ago
I think what you are doing is an overkill tbh. Google provides a nice little wrapper around their APIs via Google Apps Script, which allows you to use JS to interact directly without much overhead. You can use a JS eml parser [1] for this convert it into a single file dependency using esbuild [2], which will allow you to easily work inside the App Script environment [1]: https://www.npmjs.com/package/eml-parser https://www.npmjs.com/package/eml-parser [2]: https://stackoverflow.com/a/68379707/9496502 https://stackoverflow.com/a/68379707/9496502
- mananaysiempre 5y ago
  Offtopic: the design of Google Apps Script puzzles me. It’s done in an extremely verbose OO style (everything must be treated as an active object, even things that are by their nature dumb data), tries to compensate for that by providing shortcuts for the resulting multiple-level accessor chains, but with no rhyme or reason wrt which shortcuts exist and which don’t. There are also some things that ought by the nature of the problem exist in the implementation, but are not exposed, and the official docs literally have you reimplementing them (IIRC an official way to see which Sheets rows are selected by the currently active Sheets filter was only provided relatively recently, and you still have to redo date formatting yourself). And maybe the verbosity is no big deal for the original consumers of the API, but in the GAS environment my impression is that every method call is an RPC that takes tens of milliseconds. The bottom line for me was that fetching a couple thousand cells from Sheets as a JS array and then just processing them in JS without touching any of the Apps interfaces turned out to be a couple of orders of magnitude faster than trying to figure out exactly which cells I needed and fetching then individually, even if I only really needed a couple dozen in the end. I guess the real question is: why would anyone do it this way? I recognize that what the API is doing is in fact much more complex than it appears, because it’s a complicated distributed system (then again, when it was a VBA macro running on my desktop it didn’t need to be...), but why does it give the impression that noöne actually cares about the ergonomics? Even its original Javaish ergonomics, let alone given the fact that JavaScript is not Java.
  - mynameismon 5y ago
    > The bottom line for me was that fetching a couple thousand cells from Sheets as a JS array and then just processing them in JS without touching any of the Apps interfaces turned out to be a couple of orders of magnitude faster than trying to figure out exactly which cells I needed and fetching then individually, even if I only really needed a couple dozen in the end. Weird, when I had worked with Google Apps Script, it had been significantly faster than working with the APIs by an order of magnitude or so. (although the feeback loop is still painfully slow)
    - mananaysiempre 5y ago
      Not what I meant: in GAS, batch-fetching everything (and processing it in JS) was faster than individually fetching the things I needed (and doing much less processing on them). Does Google want me to run more JS on their machines?..
  - waxdotrun 5y ago
    Agreed, GAS is a great idea, horribly executed. It's part of the reason I made Wax[0]. It lets people run Python + SQL from Sheets with zero infrastructure. It's like GAS built for less technical people that already know some Python or SQL. It's also already integrated with other tools (e.g. databases, Slack, etc.). If anyone has internal apps that are heavily dependent on Sheets, I'd really appreciate your feedback. 0 - https://www.wax.run/ https://www.wax.run/
- soared 5y ago
  My coding knowledge is limited to barely editing existing code :). Everything I was doing is with no code, similar to what the article is about. But yeah if there were a zap for .eml->.txt or to grab text from inside an email’s attachment the whole thing would take 1 step. Often times without coding you have to take roundabout ways to accomplish something, but as long as it’s not slow and won’t break it doesn’t really matter.
mananaysiempre 5y ago
IIRC an eml is basically raw MIME data, so you need a MIME parser of some sort. The Python stdlib has one, for example. (Why can’t you then just push the CSV into Sheets using the Sheets API? Admittedly it’s supremely unpleasant, slow, and the client libraries are massive, but it does work.)
- soared 5y ago
  Haha well like in the article, I don’t know how to code and am just using automation tools with guis.
  - mananaysiempre 5y ago
    Hmm. OK. I’m not sure how to say this without sounding pretentious, and that’s especially frustrating when my point is that the whole thing is much simpler than it appears, but I’ll try and hope you give my communication skills some slack. I think you should just take the plunge. By that I don’t mean that you must or that you ought to. For example, if the problem you described is one you hate so much that you’d rather not spend another second thinking about it, by all means, disregard everything I say here, go and do whatever it is you’d rather be doing. If you have a quarterly report to prepare by the end of the week, just get it done in whatever way you know you can. I don’t mean that you should stop. I mean that your skill has surpassed your tools. Imagine someone who needs to have a short, mostly predictable conversation in a foreign language. Understandably, they turn to a phrasebook and maybe a machine translation engine and cobble together enough phrases for their task. Maybe those phrases weren’t perfect, maybe they weren’t even grammatical, but that didn’t really matter: they needed to communicate, they succeeded, end of story. Moreover, their approach was genuinely better than going to a language school: it would’ve taken months, at least, to get at the necessary phrases that way, whereas phrasebook plus MT probably got them there in hours. If that person then needed to communicate some more, maybe on a slightly different topic, repeating the same approach would be completely reasonable. It might even get faster and easier the next time around, now that they have some idea of what they are looking for and how things work generally. The third time, it’s probably still the same, even if they need to go to the library and go through a couple of phrasebooks to find the necessary sentence templates. At some point, investing in a better, spiffier phrasebook becomes reasonable. Eventually, though, the whole thing becomes silly. The tasks are probably not getting any simpler, but the phrasebook is just as limited as before. Sentence structures are simplistic, necessitating chopped speech and contortions to get the causal or temporal structure across. Idioms are lacking, leading to verbatim translations and circumlocutions that need several rounds of back-and-forth before understanding is reached. Bizarre pronunciation means listeners need to strain their ears and get annoyed. At the same time, a substantial amount of knowledge has probably been absorbed, just through repeated exposure, it’s just that it doesn’t neatly fit into the first chapter of a textbook but is more smeared over several years’ worth of conventional teaching aids. This is not the point to conclude that the language is just weird and occasionally insane and requires painful workarounds to express some things. Even if it is so, a bit, no amount of phrasebooks is enough to give an accurate idea of where and in which ways. That is the point to go find an introductory course. Moreover, this is the point to shine in an introductory course, because things will just click here and there and former mysteries will just dissolve. (Alternatively, this is the point to hire a private tutor who can find the pain points and hit them, given enough stubbornness or arrogance to not get discouraged.) It seems to me that this is the point you are at in terms of automating things with computers. The thing is, coding is easy. Programming is hard, it’s a craft that you can study for a lifetime and still have a lifetime’s worth of improvement left. If there’s an authority who can tell you how to go about learning to program, I’m not it. But coding satisfactorily is a matter of a couple of months, and getting out the first lines of code that visibly do stuff is literally a matter of typing them in. In other words, using text to tell a computer to do something is completely straightforward; that’s literally what it’s for, after all. The difficult part is thinking of a way it should go about accomplishing what you want it to, because it’s really, really dumb and literal, so it needs really detailed and formalistic instructions. Fortunately, that is the part you already have practice in! You might not have used letters and punctuation to phrase your programs, and the environment you’re used to fitting your objectives into might have simple and complicated things entirely the wrong way around compared to how they really are at the fundamental level, but you do have the mindset for describing tasks in a formal framework, and that’s really the only thing that’s painful to learn. Sincerely, a guy who got started by failing to understand a (really trashy) VBA (Office macros) doorstopper, then learned by looking at the results of the macro recorder and attempting to make thingies in PowerPoint go whee. [This text was originally meant as a preface to a short Python script that would extract CSV attachments from emails and push them to Google Sheets, but it kind of got out of hand and I need to sleep. Watch this space, hopefully I’ll post a prototype here tomorrow.]

by soared 5y ago

mynameismon 5y ago

I think what you are doing is an overkill tbh. Google provides a nice little wrapper around their APIs via Google Apps Script, which allows you to use JS to interact directly without much overhead. You can use a JS eml parser [1] for this convert it into a single file dependency using esbuild [2], which will allow you to easily work inside the App Script environment [1]: https://www.npmjs.com/package/eml-parser https://www.npmjs.com/package/eml-parser [2]: https://stackoverflow.com/a/68379707/9496502 https://stackoverflow.com/a/68379707/9496502

mananaysiempre 5y ago
Offtopic: the design of Google Apps Script puzzles me. It’s done in an extremely verbose OO style (everything must be treated as an active object, even things that are by their nature dumb data), tries to compensate for that by providing shortcuts for the resulting multiple-level accessor chains, but with no rhyme or reason wrt which shortcuts exist and which don’t. There are also some things that ought by the nature of the problem exist in the implementation, but are not exposed, and the official docs literally have you reimplementing them (IIRC an official way to see which Sheets rows are selected by the currently active Sheets filter was only provided relatively recently, and you still have to redo date formatting yourself). And maybe the verbosity is no big deal for the original consumers of the API, but in the GAS environment my impression is that every method call is an RPC that takes tens of milliseconds. The bottom line for me was that fetching a couple thousand cells from Sheets as a JS array and then just processing them in JS without touching any of the Apps interfaces turned out to be a couple of orders of magnitude faster than trying to figure out exactly which cells I needed and fetching then individually, even if I only really needed a couple dozen in the end. I guess the real question is: why would anyone do it this way? I recognize that what the API is doing is in fact much more complex than it appears, because it’s a complicated distributed system (then again, when it was a VBA macro running on my desktop it didn’t need to be...), but why does it give the impression that noöne actually cares about the ergonomics? Even its original Javaish ergonomics, let alone given the fact that JavaScript is not Java.
- mynameismon 5y ago
  > The bottom line for me was that fetching a couple thousand cells from Sheets as a JS array and then just processing them in JS without touching any of the Apps interfaces turned out to be a couple of orders of magnitude faster than trying to figure out exactly which cells I needed and fetching then individually, even if I only really needed a couple dozen in the end. Weird, when I had worked with Google Apps Script, it had been significantly faster than working with the APIs by an order of magnitude or so. (although the feeback loop is still painfully slow)
  - mananaysiempre 5y ago
    Not what I meant: in GAS, batch-fetching everything (and processing it in JS) was faster than individually fetching the things I needed (and doing much less processing on them). Does Google want me to run more JS on their machines?..
- waxdotrun 5y ago
  Agreed, GAS is a great idea, horribly executed. It's part of the reason I made Wax[0]. It lets people run Python + SQL from Sheets with zero infrastructure. It's like GAS built for less technical people that already know some Python or SQL. It's also already integrated with other tools (e.g. databases, Slack, etc.). If anyone has internal apps that are heavily dependent on Sheets, I'd really appreciate your feedback. 0 - https://www.wax.run/ https://www.wax.run/
soared 5y ago
My coding knowledge is limited to barely editing existing code :). Everything I was doing is with no code, similar to what the article is about. But yeah if there were a zap for .eml->.txt or to grab text from inside an email’s attachment the whole thing would take 1 step. Often times without coding you have to take roundabout ways to accomplish something, but as long as it’s not slow and won’t break it doesn’t really matter.

mananaysiempre 5y ago

IIRC an eml is basically raw MIME data, so you need a MIME parser of some sort. The Python stdlib has one, for example. (Why can’t you then just push the CSV into Sheets using the Sheets API? Admittedly it’s supremely unpleasant, slow, and the client libraries are massive, but it does work.)

soared 5y ago
Haha well like in the article, I don’t know how to code and am just using automation tools with guis.
- mananaysiempre 5y ago
  Hmm. OK. I’m not sure how to say this without sounding pretentious, and that’s especially frustrating when my point is that the whole thing is much simpler than it appears, but I’ll try and hope you give my communication skills some slack. I think you should just take the plunge. By that I don’t mean that you must or that you ought to. For example, if the problem you described is one you hate so much that you’d rather not spend another second thinking about it, by all means, disregard everything I say here, go and do whatever it is you’d rather be doing. If you have a quarterly report to prepare by the end of the week, just get it done in whatever way you know you can. I don’t mean that you should stop. I mean that your skill has surpassed your tools. Imagine someone who needs to have a short, mostly predictable conversation in a foreign language. Understandably, they turn to a phrasebook and maybe a machine translation engine and cobble together enough phrases for their task. Maybe those phrases weren’t perfect, maybe they weren’t even grammatical, but that didn’t really matter: they needed to communicate, they succeeded, end of story. Moreover, their approach was genuinely better than going to a language school: it would’ve taken months, at least, to get at the necessary phrases that way, whereas phrasebook plus MT probably got them there in hours. If that person then needed to communicate some more, maybe on a slightly different topic, repeating the same approach would be completely reasonable. It might even get faster and easier the next time around, now that they have some idea of what they are looking for and how things work generally. The third time, it’s probably still the same, even if they need to go to the library and go through a couple of phrasebooks to find the necessary sentence templates. At some point, investing in a better, spiffier phrasebook becomes reasonable. Eventually, though, the whole thing becomes silly. The tasks are probably not getting any simpler, but the phrasebook is just as limited as before. Sentence structures are simplistic, necessitating chopped speech and contortions to get the causal or temporal structure across. Idioms are lacking, leading to verbatim translations and circumlocutions that need several rounds of back-and-forth before understanding is reached. Bizarre pronunciation means listeners need to strain their ears and get annoyed. At the same time, a substantial amount of knowledge has probably been absorbed, just through repeated exposure, it’s just that it doesn’t neatly fit into the first chapter of a textbook but is more smeared over several years’ worth of conventional teaching aids. This is not the point to conclude that the language is just weird and occasionally insane and requires painful workarounds to express some things. Even if it is so, a bit, no amount of phrasebooks is enough to give an accurate idea of where and in which ways. That is the point to go find an introductory course. Moreover, this is the point to shine in an introductory course, because things will just click here and there and former mysteries will just dissolve. (Alternatively, this is the point to hire a private tutor who can find the pain points and hit them, given enough stubbornness or arrogance to not get discouraged.) It seems to me that this is the point you are at in terms of automating things with computers. The thing is, coding is easy. Programming is hard, it’s a craft that you can study for a lifetime and still have a lifetime’s worth of improvement left. If there’s an authority who can tell you how to go about learning to program, I’m not it. But coding satisfactorily is a matter of a couple of months, and getting out the first lines of code that visibly do stuff is literally a matter of typing them in. In other words, using text to tell a computer to do something is completely straightforward; that’s literally what it’s for, after all. The difficult part is thinking of a way it should go about accomplishing what you want it to, because it’s really, really dumb and literal, so it needs really detailed and formalistic instructions. Fortunately, that is the part you already have practice in! You might not have used letters and punctuation to phrase your programs, and the environment you’re used to fitting your objectives into might have simple and complicated things entirely the wrong way around compared to how they really are at the fundamental level, but you do have the mindset for describing tasks in a formal framework, and that’s really the only thing that’s painful to learn. Sincerely, a guy who got started by failing to understand a (really trashy) VBA (Office macros) doorstopper, then learned by looking at the results of the macro recorder and attempting to make thingies in PowerPoint go whee. [This text was originally meant as a preface to a short Python script that would extract CSV attachments from emails and push them to Google Sheets, but it kind of got out of hand and I need to sleep. Watch this space, hopefully I’ll post a prototype here tomorrow.]