Copy Files in Google Drive for New Rows in Google Sheets
With Document Studio, you can create copy of a file in Google Drive and share with one or more users from new rows in Google Sheets.
For instance, here we have a Google Sheet containing customer records and we want to send a copy of the product brochure to each customer. The file exceeds 50 MB in size and thus cannot be shared over email so we'll make a copy of the file for each customer and share it with the customer.
Copy Files with Google Drive
Install the Document Studio add-on and open your Google Sheet to launch the add-on. You may either open an existing Google Sheet or create a new sheet and add data in one or more rows of the sheet.
Inside the Document Studio app, provide a descriptive name for your workflow and choose the specific worksheet in your Google Spreadsheet that contains the merge data. Click on Continue to move to the conditions page.
Inside the Conditions section, specify if you want your workflow to run only if the row data satisfies certain conditions. Next, choose Google Drive from the list of available tasks.
Configure Google Drive Service
Choose Copy File as the Drive Service and select the source PDF file in Drive that you would like to share with customers.
Next, choose the folder in Drive that you would like to copy the file to. The folder could be in your own Google Drive or a Shared Drive (Team Drive) folder. You can also specify a dynamic sub-folder in the parent folder by using the curly braces notation.
For instance, if you specify /{{Country}}/{{Customer Name}} as the sub-folder, the files be added to that dynamic sub-folder in the parent folder. The folder structure will be created if it does not exist.
The name of the copied file can be customized to include the customer's data from the sheet row. For instance, you could specify the copied file as {{Customer Name}}.{{File Extension}} and the customer's name would be added to each file while retaining the original file extension.
Share Files in Google Drive
You may put the email address question in the Editor or View section using curly braces - like {{ Email Address }} - to automatically share the copied file with the customer. You can also specify other email addresses to share the file with.
Preview and Save
Click the Preview button and you should see a copy of the original file in your Google Drive created using data from the first row in the Google Sheet. Click Done to locally apply your changes.
Click on Continue to proceed to the triggers screen. If you want the workflow to be automatically triggered every hour, so that any new rows added in that duration are processed automatically, you can choose the option Run workflow every hour and then click on the Save button.
The workflow will now appear in the workflow dashboard where you can edit, delete or manually run the workflow.
If you would like to copy multiple files in Google Drive for the same row, you can add multiple Google Drive tasks in the same workflow.