Google Apps Script – still a helpful tool to automate repetitive tasks in Google Docs with user input

Awhile back, I shared a post about using Google Apps Script (GAS) to automate repetitive tasks in Google Docs. 4 years later, we are still using this same script.

My church recently announced that we would be launching an evening service, so I took the opportunity to open the script and add support for the additional document type. I also added conditional checks that if the user ever selects the ‘CLOSE’ button on one of the dialog boxes, the script will automatically end and no further action is taken.

Here’s the updated script:

function myFunction()
{
  var ui = DocumentApp.getUi();
  
  var templateDocId = '[templateid]';
  
  var prompt_numberOfDocs = ui.prompt('How many Talking Point documents do you want to create?');

  if (prompt_numberOfDocs.getSelectedButton() == ui.Button.CLOSE) {
    // don't go any further
    return;
  }

  var prompt_startingDate = ui.prompt('What is the starting date? Please enter in MM/dd/yyyy.');

    if (prompt_startingDate.getSelectedButton() == ui.Button.CLOSE) {
    // don't go any further
    return;
  }
  
  var numberOfDocs = parseInt(prompt_numberOfDocs.getResponseText());
  var startingDate = prompt_startingDate.getResponseText();
  
  var prompt_venueResponse = ui.prompt('Venue', 'Create Documents for a custom venue? Type in the venue name and click YES. Otherwise click NO.".', ui.ButtonSet.YES_NO);
  
  var venueTitle = '';
  
  var customVenue = false;

  var createAud1Morning = false;
  var createAud2Morning = false;
  var createAud1Evening = false;

  if (prompt_venueResponse.getSelectedButton() == ui.Button.CLOSE) {
    //don't go any further
    return;
  }
  else if (prompt_venueResponse.getSelectedButton() == ui.Button.YES)
  {
    venueTitle = prompt_venueResponse.getResponseText();
    customVenue = true;
  }
  else {
    var prompt_Aud1MorningResponse = ui.prompt('Venue', 'Create Documents for Aud 1 (Morning)?', ui.ButtonSet.YES_NO);
    var prompt_Aud2MorningResponse = ui.prompt('Venue', 'Create Documents for Aud 2 (Morning)?', ui.ButtonSet.YES_NO);
    var prompt_Aud1EveningResponse = ui.prompt('Venue', 'Create Documents for Aud 1 (Evening)?', ui.ButtonSet.YES_NO);

    if (prompt_Aud1MorningResponse.getSelectedButton() == ui.Button.CLOSE) {
      //don't go any further
      return;
    }
    else if (prompt_Aud1MorningResponse.getSelectedButton() == ui.Button.YES) {
        createAud1Morning = true;
    }

   if (prompt_Aud2MorningResponse.getSelectedButton() == ui.Button.CLOSE) {
      //don't go any further
      return;
    }
    else if (prompt_Aud2MorningResponse.getSelectedButton() == ui.Button.YES) {
        createAud2Morning = true;
    }

    if (prompt_Aud1EveningResponse.getSelectedButton() == ui.Button.CLOSE) {
      //don't go any further
      return;
    }
    else if (prompt_Aud1EveningResponse.getSelectedButton() == ui.Button.YES) {
        createAud1Evening = true;
    }
  }
  
  var date = new Date(startingDate);
  
  var htmlOutput = HtmlService
    .createHtmlOutput('<p>Creating ' + numberOfDocs + ' documents. Please stand by...</p>')
    .setWidth(300)
    .setHeight(100);
  
  ui.showModalDialog(htmlOutput, 'Talking Points - Task Running');
  
  for (var i = 0; i < numberOfDocs; i++)
  {
    var loopDate = new Date(date.getTime()+ ((i * 7) * 3600000 * 24)); // uses the looping interval to get the starting date and add 7 days to it, creating a new date object
    var documentName = 'Talking Points - ' + Utilities.formatDate(loopDate, Session.getScriptTimeZone(), "MMMM dd, yyyy");
    var documentDate = Utilities.formatDate(loopDate, Session.getScriptTimeZone(), "MM/dd/yyyy");

    if (customVenue) {
      documentName += ' (' + venueTitle + ')';
      createNewTalkingPointDocument(templateDocId, documentName, venueTitle, documentDate);
    }
    else {
      if (createAud1Morning) {
        createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 1 Morning)', 'Aud 1 (Morning)', documentDate);
      }

      if (createAud2Morning) {
        createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 2 Morning)', 'Aud 2 (Morning)', documentDate);
      }

      if (createAud1Evening) {
        createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 1 Evening)', 'Aud 1 (Evening)', documentDate);
      }
    }
  }
  
  htmlOutput = HtmlService
    .createHtmlOutput('<script>google.script.host.close();</script>')
    .setWidth(300)
    .setHeight(100);
  ui.showModalDialog(htmlOutput, 'Talking Points - Task Running');
}

function createNewTalkingPointDocument(templateDocumentId, documentName, venueTitle, documentDate)
{
  //Make a copy of the template file
  var documentId = DriveApp.getFileById(templateDocumentId).makeCopy().getId();
      
  //Rename the copied file
  DriveApp.getFileById(documentId).setName(documentName);  
      
  //Get the document body as a variable
  var body = DocumentApp.openById(documentId).getBody();
    
  //Insert the entries into the document
  body.replaceText('##VENUE##', venueTitle);
  body.replaceText('##DATE##', documentDate);
}

If you’re using Google Apps Scripts to automate something, leave a comment to share how! If you’d like to create something like this for your ministry and need my help, drop me a line.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s