0

I'm working on generating a regularly updated report. Currently the database I'm accessing only supports scheduled exports of data to an email. (Formatted as a CSV, XLSX, or PDF)

This would be fine, but I need to do post processing before the report is in a presentable manner. With computing automating repeated processes is ideal.

So have data coming in as CSV attachments, every email has the same title format, and every CSV report has the same number of columns.

I'm fine building a multiple step process, but I want to figure out an efficient way to get all the exported reports to be merged into a spreadsheet as they roll in, so that I can increase the frequency of the reports.

5
  • Hi and welcome to WebApps. Have you read Google Gmail script that triggers on incoming email on StackOverflow? I think this at the core of your solution. My main concern would be assignment of gmail labels to the incoming emails given that all the mail has the same title, and all the attachments have the same name. Would you elaborate on this?
    – Tedinoz
    Commented Jan 29, 2022 at 2:29
  • I see. Step one: Apply a filter to incoming messages of a specified format. Step two: Use this code to run a script to download all the attachments (or add them to the drive)and remove the label. Step three: find a way to either merge them or have a sheet call from a folder of them.
    – Russell
    Commented Jan 31, 2022 at 14:35
  • Yep. I'd add... #1: use a file-naming method for attachments so each has a unique and relevant name. E.g., a date prefix, like: "20220101-iamacsvfile.csv" and "20220101-Iamanothercsvfileforthesamedate.csv". #2 in Gmail, rename the saved attachments. #3: in Sheets, use a cell to manually enter the unique csv file-identifier for files to be imported. For example, "20220101". #4: use a script that references the unique identifier as the basis for selecting files to import. #5: for each report, insert the names of "imported" file as a quick visual proof that the correct files were imported.
    – Tedinoz
    Commented Feb 2, 2022 at 1:14
  • figure out an efficient way to get all the exported reports to be merged into a spreadsheet as they roll in Hmm. Just reflecting on this. My previous comment assumed a manually triggered update, but I can see you are envisioning an automatic update. Question: is there only one csv file to be imported for the report?
    – Tedinoz
    Commented Feb 2, 2022 at 1:17
  • These posts from StackOverflow might help (or not :) ) with triggering Google gmail script that triggers on incoming email and How to trigger a Google Apps Script once an email get in the inbox?.
    – Tedinoz
    Commented Feb 2, 2022 at 1:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.