I have a spreadsheet in Excel that is periodically updated throughout the day with new rows. Each row includes a date. What I’d like to do is, once monthly, export rows with dates from the previous month as a CSV and email it.
I figure my Zap will utilize Schedule by Zapier and (maybe) @ikbelkirasan’s excellent Advanced Utilities integration. I’m just not really sure how to go about making sure I’m only capturing the rows from the previous month. What do you think your approach to this would be?
I’m not opposed to thinking differently about, what is essentially, a scheduled report. Though I would prefer to keep the data in Excel. I could be talked out of that.
Views can be exported as CSV (not programmatically)
Airtable has APIs for exporting records.
@Troy Tessalone, I’d considered AirTable and it looks pretty rad. Just trying to avoid another subscription. Checking Transfer out now. Forgot that existed. Will post back with my findings. Thanks!
@ericg
Depends on your requirements and number of records, but Airtable has a free plan.
Tried Transfer. Going from Excel to Excel (both same account). Ran into an “unexpected error.”
I just realized that, another reason I need to stay in Excel, is because the worksheet I would export to CSV is also used in another worksheet to generate a different type of report. If I were to keep that in place, I think the best I could do is have the rows zapped over to both Excel and Airtable.
http://parabola.io seems like it could be a potential tool for this, too.
Hey @ericg! Were you able to figure out a workflow here?
@TamRazzleDazzle, I have not been successful in automating my entire process and am not sure I will. Maybe time for a proper ERP.
That said, Airtable is a good solution for what I originally needed in this thread.
Hey @ericg!
This is a little workaround I came up with that *should* do the trick.
With Google Drive (and Dropbox) you can enter text into the Upload File action:
Specifying the extension means that it will become a CSV:
Now, this is the important part. When rows are added to your spreadsheet and they include a date, will that date stay the same? And will the row always remain?
In other words, could you trigger on rows being added (and filter on the date having to include something that indicates it’s from the month you’re interested in) and then add them to Storage or Digest by Zapier?
Or, is it the case that a row might be added, have the month you want, but by the time you’d want to create the CSV it may have changed or been deleted?
That will determine whether my idea can work. Let me know!
@nicksimard, the row date would stay the same and, once a row is added, it will remain in the sheet.
Hey@ericg!
Thanks for confirming.
It seems like my idea could work. After thinking it through some more, I realized it’s kind of complex and there are a couple of sticky points.
If you’re up for the challenge, let me know and I’ll lay it all out so you can take a stab at it :)
Hey @ericg! Just checking in here to see if you saw Nick’s reply. Let us know if you’re up for partnering together to solve this one!
Hey@ericg!
Thanks for confirming.
It seems like my idea could work. After thinking it through some more, I realized it’s kind of complex and there are a couple of sticky points.
If you’re up for the challenge, let me know and I’ll lay it all out so you can take a stab at it :)
Howdy! Sorry for the delay. Other projects have taken priority. That said, I’m willing to give something complex a shot but I’m not sure when I’ll be able to get to it. Appreciate your help!
Hey@ericg,
I should preface this by saying that I’m far from an expert when it comes to Excel, but here’s a pretty concise way you could do it.
Based on this, I figured out how you can auto-add a formula to new rows in Excel.
In Excel
I used this formula…
=IF(MONTH(C2)=MONTH(TODAY()),"Yes","No")
… to see if a date is within the current month:
Trigger: Excel — New Row
This part is straightforward. trigger on new rows being added to your desired spreadsheet/worksheet.
Action 1: Filter
Only continue if that formula field says “Yes”:
Action 2: Digest —Append Entry and Schedule Digest
In this one step we append the items to the digest as well as define when we want it to be released. Midnight on the 1st is right after the very end of the previous month, so that makes sense to release.
NOTE: You can also set up a separate Zap to release this, by triggering it Schedule by Zapier.
(Optional) Delay by Zapier
If you don’t want to send the email right away, you can add a Delay by Zapier step, and specify the number of hours to delay the next action by.
Action 2: Dropbox — Upload File (you could also use Google Drive)
This step creates the CSV by using the digest you just released. Make sure you add the header row by typing the column names as they’ll appear in your digest.
Action 3: Email
Now you can email the CSV file, by mapping this field into your Attachments field:
Whew, ok. Hopefully this helps! It’s ONE way you could tackle this, and without making the Zap too complex :)
@nicksimard Wow! This is clever and efficient. I could see all sorts of uses for this sort of thing. I’ll add this to my workflow! Much appreciated:-)
