Category: Uncategorized

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.

About a year and a half ago, I shared our project where we were able to gain network control of our Marshall cameras by bridging RS485 over UDP with a Raspberry Pi and a USB to RS485 adapter.

I’m happy to report that it is still working great, week after week. In fact, we recently implemented a second system in our other auditorium, so when we have simulcast services and I want to move a camera over there, it’s still just as easy to shade them remotely.

I have had several people write in and ask specifically how we created the wiring to make this possible, so I drew up this diagram today:

Essentially here, serial is two wires, positive (+) and negative/minus (-). So the cables use the blue/white blue pair of standard cat5/6 cable to make the connections. In our setup, we have wall/stage boxes with standard cat6 jacks that lead to a cat6 patch panel in the rack rooms where we have the raspberry pi located. So we made adapters that allow us to use standard cat6 cables to make all of our connections. We chose the blue/white blue pair as it is data only to minimize any risk of a misconnection or short if it were accidentally used for something else on stage or if someone else plugged in the wrong cable to our adapter.

Hope this helps as you implement in your enviroment!

Somehow, it’s been a year since I’ve posted to this blog. I’ve been busy! Some financial circumstances in my family have meant I have needed to spend a lot of my spare time coding-for-hire. Over the past year, I’ve been developing custom apps, writing so many Companion modules, creating Ross Dashboard custom panels, and developing unique solutions through software development to meet the demands for clients. God continues to bring people to us who need my help, and He meets our needs.

I have also been hard at work developing solutions for the church.

• Tally Arbiter is now in version 3.0 and continues to grow in support for more switchers and add more features.
• PresentationBridge-Client has gained more features and continues to be a helpful resource for helping to automate church production.
• I’ve written several more plugins for Cronicle to help with the various workflows around our facility. Every venue we support is managed through Cronicle now. As a room is reserved, we create the automated events, and people just show up! The AVL of the room is turned on and ready for them, automatically configured to meet the needs of their event.
• TimeKeeper got some feature bumps as well, allowing us to easily automate things like switching OBS scenes “3 seconds before x timer ends”, etc. We even have it turning off the lights in the control room just as production is about to begin!
• We are doing more and more with Companion to help our volunteers do more at the press of a button. Complex tasks on a video switcher are easy because they just press one button.

I’m excited to share my latest project here today that helps us meet a specific need at our church, and I hope will help you at yours. We use Spotify around the campus as people are walking in, pre-service music, etc. We have a dedicated Mac Mini running Dante Virtual Soundcard that outputs the Spotify music to our Dante VLAN. From there, any of the venues can receive the Spotify mix and play it – whether it is one of the auditoriums, the cafe/coffee bar, the outdoor speakers as people walk in, etc.

We run this Campus Spotify headless on the Mac mini so it’s located in a rack room where we never touch it. When we need to change playlists, we remote into the computer and control it. When we need to start/stop playing, we’ve been triggering an AppleScript file remotely to play/pause.

But that all just got a lot easier. I recently developed a node.js/electron app, for MacOS only, that will run in the system tray of this Campus Spotify machine. It controls Spotify using AppleScripts, and it gets data out of Spotify, like the current playing track, through Apple’s built-in Distributed Notification Center, which Electron can easily pick up and process. (Electron is the framework that allows you to create cross-platform “native” apps that can be run on the computer without having to install the node.js runtime. You might be surprised to learn that several apps you already use are written in node.js and use Electron!)

Why not just use the Spotify Web API? Sure – you could gain control of Spotify through their Web API – but for some users, the OAuth process and creation of App Keys is a stumbling block. This process is much easier – just download the app and run it!

To enable remote control, this app exposes both a simple REST API as well as socket.io connections for real-time event based communication. Whenever Spotify changes tracks or play/pauses, it will automatically communicate this information to any of the connected socket.io clients.

I’m calling the app, “spotify-controller”.

I’ve also created a partner Companion module to work with it. It supports variables, feedbacks, and presets to make things a lot easier to control!

Like all of the software I write for the church, this is free and open-source. My hope is that it will not only help you use technology more effectively for ministry, but by releasing it open source, it will continue to foster a community for contribution. We can do so much more together when we share resources and time.

You can download this new app today over at my Github repository: http://github.com/josephdadams/spotify-controller/

The partner Companion module is available in the beta builds as well. If you’re a Cronicle user like us, the plugin to control it will be available here: https://github.com/josephdadams/cronicleplugins