Complex Transfer of Data from Email Attachment to Template

Question

I have a complicated project I am undertaking for my work, and any advice you could give would be really appreciated.

Basically here is what I want to do:

1. Filter Emails from specific people
2. Download those emails' attachments (they are excel format) to a specific folder in Drive (called "Input")
3. Pull the data from those spreadsheets to specific cells of another spreadsheet (master template) I have in another part of Drive (probably over a hundred cell value transfers)
4. Automatically delete the downloaded attachments in the drive to prepare for the next (same name) files that will be downloaded tomorrow.
5. Once the master is filled up, make a copy of the entire spreadsheet, rename it to today's date, and then wipe the master to rinse and repeat the same process tomorrow.

So right now I am just working with this conceptually, here is a basic blueprint of what I am thinking about doing but your input would be much appreciated:

1. Filter emails using Gmail's Label system

2. Use this method to download emails: https://www.splitbrain.org/blog/2017-01/30-save_gmail_attachments_to_google_drive

3. Create a permanent spreadsheet in the "Input" folder (called "Master Array") to array all the data from the downloaded spreadsheets and their respective sub-sheets. The goal here being to have one constant File_ID housing all the data.

4. Create a search if array function in the Master Array which will search for the correct files by their respective name and array their data in the correct subsheets in the Master Array spreadsheet (i.e. If file name contains "Company Sales" array data in "Sales" subsheet).

5. ImportRange, Query, Vlookup, etc. that "Master Array" spreadsheet and pull all the values I need out of there to the respective cells in the Master Template they need to be.

6. Once the Master Template is built, I want to copy the entire spreadsheet, rename it to today's date, and then wipe the original (preparing it for the same function tomorrow). (using google timer trigger)

7. Delete all the downloaded email attachments in the drive folder "Input" to rinse and repeat for the same function the next day. (using google timer trigger).

Some questions I have:

1. Is there a more efficient way to do this?

2. What is the best way to copy data from one spreadsheet to another, would it be quicker in script, or as a import function within each individual cell of the master template?

3. Can I use a loop/if function to pull certain cells to certain sheets within the Master Template, basically having functions for each sheet name, so say IF sheetname="Sales" pull cells A2 from the other spreadsheet to b3... etc.

Sorry this is very long and robust, just wanted to see if this is possible to do comprehensively or not. Thank you for any and all input, I am relatively new to Sheets so forgive my naivety.

Answer 1

1) You can use a search query when retrieving the messages list [1][2].

2) You could develop your own code to get the messages list and get the attachments for each email using GmailApp class [3].

3, 4, 5) You'd first need to convert the excel to Google Sheets [4], to be able to manage the Spreadsheets easily with SpreadsheetApp class [5].

6) You can copy the Spreadsheet like this [6] and get the entire range of data to clear it [7].

7) Use this [8].

[1] https://developers.google.com/gmail/api/guides/filtering

[2] https://support.google.com/mail/answer/7190

[3] https://developers.google.com/apps-script/reference/gmail/gmail-app

[4] Converting .xls to google spreadsheet in google apps script

[5] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

[6] Google Apps Script, copy one spreadsheet to another spreadsheet with formatting

[7] https://developers.google.com/apps-script/reference/spreadsheet/range#clear()

[8] https://developers.google.com/apps-script/reference/drive/drive-app#removeFile(File)