Tuesday 29 June 2021

Bulk create Sheets from a Google Sheet

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.

Bulk create Google Sheets from a Sheet of data by looping through each row.
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:

  1. The ID of the Google Drive folder where you wish to store the newly created Google Sheets.
  2. The ID of the template Google Sheet that is to be copied for each row.
  3. 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.
  4. 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).

Labels: , ,

9 comments:

  1. Ruller19 April 2022 at 12:35

    How can i use that script only for las entry row! with trigger on submit form? thanks for your great job!!

    ReplyDelete
    Replies
    1. Phil Bainbridge20 April 2022 at 10:54

      Hi

      Are 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

      Delete
    2. Ruller21 April 2022 at 09:49

      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

      Delete
    3. Ruller21 April 2022 at 09:52

      This comment has been removed by the author.

      Delete
    4. Phil Bainbridge22 April 2022 at 11:21

      Hi

      I'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

      Delete
    5. Ruller4 May 2022 at 11:40

      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.
      thank you Mr Phil

      Delete
    6. Phil Bainbridge8 May 2022 at 18:07

      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.
      Once you've done that bit, you can use the code on this blog post to paste over the values.

      Kind regards
      Phil

      Delete
  2. Unknown21 April 2022 at 02:44

    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

    ReplyDelete
    Replies
    1. Phil Bainbridge22 April 2022 at 10:13

      Hi

      Yes 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

      Delete

Subscribe to: Post Comments (Atom)