The following Google Apps Script is designed to bulk create Sheets from rows of data within a Google Sheet and to include some of that data within the new file in specific cells. It also creates a link to the new Google Sheet back in the original file on the relevant row.
For those wanting to bulk create Google Docs from a Google Sheet please see this blog post.
Screenshot of Google Sheets data |
It takes data from the first four columns and will copy that into the new Google Sheet template file that is created for that row.
There is a Config sheet where you will need to specify some further information:
- The ID of the Google Drive folder where you wish to store the newly created Google Sheets.
- The ID of the template Google Sheet that is to be copied for each row.
- The name of the Sheet/Tab in the template file where data is to be copied to. This option tries to make the tool universal as you may have other data in the Google Sheet that needs to be left untouched.
- The four cell references for each piece of data that it to be copied in to the newly created Google Sheet. Again to be universal this allows you to specify exactly which cells you want the information to be put into.
Download
Bulk create Sheets from a Google Sheet download here (please use 'File' > 'Make a copy' for your own version).
How can i use that script only for las entry row! with trigger on submit form? thanks for your great job!!
ReplyDeleteHi
DeleteAre you referring more to wanting something like this: https://www.pbainbridge.co.uk/2020/05/create-google-doc-from-form-submission.html
Kind regards
Phil
exactly!im placing my orders via google form and i need to automatically creates the invoice from the last entry/row only.not the enrtries before! im using that function since today but its bulkPDf and i prefer the (EXCEL/sheet) outpout
DeleteThis comment has been removed by the author.
DeleteHi
DeleteI'm not sure exactly which bit you're stuck with? In my code/example I use a Trigger that runs upon Form submission - which means it will only run for the last item submitted. Can you take my code and adapt it?
Kind regards
Phil
to explain better ..i will use the formo to docs code but i want to generate "sheet" files not "doc" thats what i need to change only. the output format.
Deletethank you Mr Phil
Rightio, so when the Form to Doc code makes a copy of a template file (line 108 on the GitHub code) you want to be making a copy of a Google Sheet here rather than a Google Doc file.
DeleteOnce you've done that bit, you can use the code on this blog post to paste over the values.
Kind regards
Phil
Hey there, Just wondering if I can put those duplicates into corresponding folders (I have got heaps of folderID)? So far, the destfolderID is limited to only one
ReplyDeleteHi
DeleteYes technically you could. You would need a column with each Folder ID, then as you iterate through each row, use the DriveApp to get that folder and work within it.
The closest example that comes to mind is this post I have for bulk creating Drive folders and putting files into them: https://www.pbainbridge.co.uk/2021/11/bulk-create-google-drive-folders-and.html
Kind regards
Phil