Thursday 30 May 2019

Copy template sheet to Google Sheets and reorder

The following Google Apps Script was developed to take a template Google Sheet (containing fields for 3 signatures) and copy it into other Google Sheet files within a given Google Drive folder and then reorder those sheets so that it was the first one. The idea was based around needing users to authorise student marks with their signature from multiple Google Sheet reports produced from a third party system.
Screenshot of template sheet with signature fields to be copied
Template sheet with signatures to copy to other files

userPopup.gs
The script starts with a popup box requesting the ID of the Google Drive folder where the files that require the template sheet to be copied to are stored. We use 'getResponseText' to capture what the user enters into the dialog box so we can pass this through to the main Function.
var folderIDText = result.getResponseText();
copyTemplateSheet.gs
The main Function uses the folder iteration feature of Google Apps Script to loop through every file within the specified folder. To start we feed the above variable into the Function and acquire the Google Drive folder.
var folder = DriveApp.getFolderById(folderIDText);
From here we look to 'getFiles' and then cycle through each one via 'hasNext' - which means we now have a way to cycle through each file in the folder and open it.
var subFiles = folder.getFiles();
while (subFiles.hasNext()) {
   var nextSubFile = subFiles.next();
   var subFileID = nextSubFile.getId();
   var destination = SpreadsheetApp.openById(subFileID);
Now we can copy the template sheet into this destination file and rename it so that it does not have Copy of appended to it, but instead the name Intro for the purposes of this example.
var destinationSheet = destination.getSheetByName('Copy of Intro');
destinationSheet.setName('Intro');
Finally we need to reorder the sheet so that it is the first one when the file is opened. To do that we must first set our copied template sheet as the active sheet and then move it to a specified position (so 1 in this instance).
destination.setActiveSheet(destinationSheet)
destination.moveActiveSheet(1);
And there we have it!

Copy template sheet to Google Sheets folder (please use 'File' > 'Make a copy' for your own version).

1 comment:

  1. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    Electronic Signature Software

    ReplyDelete