0
231
VistasHow to create Google Sheets Macro/Apps Script to Open Newly Added File and Copy Data to Master?
I would like to create an automatic process with Apps Script to trigger on newly added excel files in Google Drive, open them, and copy the new data and append it to last row in the Master Google sheet.
The data would also need to be manipulated a bit as it comes in the format below. There will be several blocks like this for every vehicle. I would like the script to populate the vehicle # in each of the rows of available data for that vehicle in a separate column, in this case 6 rows. Then copy the data between rows labeled VEHICLE and VEHICLE TOTALS and paste it into the last row of the Master google sheet.
Data Table Example:
1 Respuestas
Responde la pregunta0
This looks to have a lot of working parts, so it'd be hard to cover in one response. But here's how I'd go about it...
- Create a trigger
- Scan the folder to find docx file
- Convert docx file to Google Doc
- Pull data from doc file to sheet
- Manipulate data accordingly Write to appropriate location
Create a trigger - This might be the hardest part. I don't think dropping it into a directory can trigger a script. Instead, you can have your scripts timed, trigger them manually, or trigger based on an event within another G Suite document (sheet, docs, forms, etc.) Maybe you can just create a Google Form that you upload the file to?
Scan the folder to find docx file - Once it's somewhere in your GDrive and you've trigger a script, you can find it using the Folder and Folder Iterator classes
Convert docx file to Google Doc - Looks pretty straightforward Docx to Google Doc using Google Script
Pull data from doc file to sheet - A combination of the Document class, Spreadsheet class, and Google Script programming know-how.
Manipulate data accordingly & Write to appropriate location - Same as above.