Monday, October 17, 2016

(SSAF) - Using Javascript with Google Sheets

Due to the beginning of the SSAF* project being very frustrating and the Google Sheets tutorials being unforgivably vague, I've decided to make a more in-depth post about a small script I wrote for my new job at Aqua Tots.

Warning: Google sheets uses bound scripts so you have to open the script from Google sheets by selecting 'Tools > Script editor'. Without doing that you won't be able see the results from your script.

The first thing we need to do is to confirm which sheet we're working on. While it's always going to be the one open, we need to confirm the link to it using a prompt. I used the following code:
function showPrompt() {
  Logger.log('(SSAF): Requesting spreadsheet link');
  var ui = SpreadsheetApp.getUi(); // Same variations.
  var result = ui.prompt(
      'Please enter the link to the schedule you would like sorted',
      '(Google Sheets links ONLY)',
  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK && text != "") {
    // User clicked "OK".
    return text;
  } else {

Which resulted in this:

Our schedules came in the following format:

But they needed to be converted into this format:

The Task:
The schedules needed a lot of work. The had to be organized by Staff /Time, the time column had to be converted to Standard format and moved to the first column and after the excess information was deleted, they needed to be indented by comparing class and staff.

The Function:
This is the function I used to sort my data and organize it as necessary. Hopefully this will help those of you struggling to integrate Google Sheets Scripts into your work.

function sortSchedule() {
   var link = showPrompt();
   var spreadsheet = SpreadsheetApp.openByUrl(link);
   var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
   var tableRange = "A2:I200"; // What to sort
   var range = sheet.getRange(tableRange);
   var lastCol = sheet.getLastColumn();
   var lastRow = sheet.getLastRow();
   Logger.log('(SSAF): Sorting ' + String(tableRange) + ' by name and time..');
   var r = sheet.getRange(2, 1, lastRow - 1, lastCol); // assumes headers in row 1
   r.sort([{ column: 5, ascending: true }, { column: 3, ascending: true}]);
   Logger.log('(SSAF): Sorting Complete!');
   Logger.log('(SSAF): Deleting extra data...');
   Logger.log('(SSAF): House keeping...'); 
     for (i = 2; i < 1000; i++) { 
       var range = sheet.getRange(i,3);
       var range2 = sheet.getRange(i+1,3);
       var range3 = sheet.getRange(i,4);
       var range4 = sheet.getRange(i+1,4);
       var data = range.getValue();
       var data2 = range2.getValue();
       var data3 = range3.getValue();
       var data4 = range4.getValue();
         if (data.localeCompare(data2) != 0 || data3.localeCompare(data4) != 0) {
           Logger.log('(SSAF): Adding indentation in row ' + i); 
           i++; // skips newly created row
         } else if (data.localeCompare("") == 0) { //empty field
           Logger.log('(SSAF): Indentation loop broken at row ' + i); 
   Logger.log('(SSAF): Moving time to Column One...');
   sheet.insertColumns(1, 1);
   var valuestocopy = sheet.getRange("E1:E1000");
   valuestocopy.copyValuesToRange(sheet, 1, 1, 1, 1000);
   sheet.deleteColumn(5); //deleting duplicate column
   Logger.log('(SSAF): Converting to standard time...'); //Column 5
   for (i = 2; i < 1000; i++) {
     var range = sheet.getRange(i,1).getValue();
     if (range == "" && sheet.getRange(i+1,1).getValue() == "") {
     Logger.log('(SSAF): It broke at ' + i);
     var h = range.substr(0,2);
     var mm = range.substr(3);
     //var m = +mm;
     Logger.log('(SSAF): time broken down ' + range + ' to ' + h + ':' + mm);
     if (h > 12) {
       var val = h +':' + mm;
       Logger.log('(SSAF): Converting ' + range + ' to ' + r); //Column 5
   Logger.log('(SSAF): Schedule Complete.');

Wednesday, April 29, 2015

Working smart, not hard

I'm currently interning at Loadmaster, an oil rig design company based in Houston. Yesterday I was tasked with writing a script to delete files in our scanned files directory that were over 45 days old.

Doesn't sound too complicated right? Well a quick google search revealed that many others have done similar projects.

Starting with a script from TechnicalKeeda as a base, I saved time by tweaking his script to my needs instead of starting from scratch. After adding a few printlns to readout more information, increasing the scan to target all file types in the directory, and changing the days required for deletion eligibility I had a working script in 15 minutes.

After that I wrote a simple batch file to run the .jar, set it up on our server's task scheduler and voilĂ . Here's my finished code, and an example of the readout.

 * deletes files in the scan directory

public class ScanDelete {

  private String dirPath = "\\\\utility\\scans";

  public static void main(String[] args) {
    ScanDelete deleteFiles = new ScanDelete();
    deleteFiles.delete(45, "");

  public void delete(long days, String fileExtension) {
    File folder = new File(dirPath);
    if (folder.exists()) {
      File[] listFiles = folder.listFiles();
      long eligibleForDeletion = System.currentTimeMillis()
        - (days * 24 * 60 * 60 * 1000L);
      for (File listFile : listFiles) {
        System.out.println("File Name: " + listFile.getName());
        System.out.println("Last modified: " + listFile.lastModified());
        System.out.println("Eligible for deletion: " + eligibleForDeletion);
        if (listFile.lastModified() < eligibleForDeletion) {
          System.out.println("Deleting File" );
          System.out.println("-----------------------------------" );
        } else {
          System.out.println("File Spared" );
          System.out.println("-----------------------------------" );
    } else {
      System.out.println("Error: Folder Doesn't Exist!");

Here's the point: One of the most valuable skills you can learn as a programmer is resource management. Take advantage of the fact that there have been thousands before you trying to solve the same problems, and don't waste time writing small scripts that have already been written for you.

Tuesday, April 28, 2015

[Release] Juche! The North Korean RPG

"The interactive text based informative thriller Juche will keep you enthralled for minutes! Nay, tens of minutes! - I.G.N.

Info about the story and earlier posts showing the progression of Juche

Dislaimer: The game isn't fully complete. Inventories, quests, and shops haven't been finished. You're welcome to do anything you'd like with the source as long as I'm credited if you release anything.

  • Amazing randomized fighting dialog
  • 6 maps, 10 enemies, tons of NPC's and Tons of Bugs  other features :)
  • Load & Save system
  • Admin command system
  • Unique NPC dialog (Including North Korean News NPC's and the tip giving Sensei of ultimate wisdom)
  • Minimalistic game art
  • Tray message system that drops alerts in the bottom right corner of screen when needed

Spoiler - Inside the source there's a list of admin commands you can use to cheat if you'd like. Enjoy!

Sunday, April 26, 2015

Pass Phrase Lite!

With the addition of grueling bug fixes, a tutorial, and the credits page, the free version of Pass Phrase is now complete! (Pass Phrase Lite)

Featuring 5 unique categories and hundreds of phrases P.P.L. sports a surprising amount of entertainment.

Unfortunately Pass Phrase Lite won't be released until Pass Phrase Plus has been finished.
Pass Phrase Plus will be available for $1 in the Windows Store, features 10 categories, anti-phrase repeat algorithms and over 800 phrases.

Tutorial & Credits page screenshots:

Sunday, March 15, 2015

Pass Phrase Rev. Two & Becoming Official!

Continuing towards my goal of being a published programmer I'm now a registered Windows Developer. As fancy as that sounds it just means I gave Windows $20 and a bunch of my personal information to gain the ability to publish apps in the store. WOO

Pass Phrase has received heavy updates as the release date closes in:
- New Simplified GUI
- Bug fixes and sound effects
- Refurbished graphics
- Hundreds of new phrases

As of right now I've been using Windows Visual Studio 2012 but I'm thinking of switching to Unity as it makes porting to other platforms incredibly easy and has better graphics handling.

Tuesday, February 17, 2015

More Windows Phone Development? Pass Phrase!

Don't worry folks, Juche is still in progress. However as the result of a recent chain of events I'm taking a semester off from school & I'm going to use this time to get a few more lucrative apps onto the Windows Marketplace.

Pass Phrase is a phrase game similar to Catch Phrase(But with way more innuendo!), where the person in possession of the phone has to make the others on their team guess the phrase using words other than those on the screen.

I've recently joined a development group called Final Turn Studios, and plan to release Pass Phrase at the end of the month under that name. Here's a few screenshots of the game as is (WIP)

Sunday, November 16, 2014

As promised I've done a little work on Juche WP edition and it's coming out surprisingly easy to port. Java and C# are highly similar in terms of syntax.

It's going to take a pretty significant amount of time to cover the extra features windows phone has to offer in terms of stacks and stackcollections, but I believe Juche WP will be here in a short matter of time.

Today I finished character creation and the stats pages to hold character information.