Google Apps Script for "Planing Poker" Response Page

gistfile1.js

/**
 * Make the moderator sheet active
 */
function gotoModeratorSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Moderator Page")
  ss.setActiveSheet(sheet)
}


function isSheetExisting(name) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  for( i in sheets ) {
    if( sheets[i].getName() == name )
      return !0;
  }
  
  return 0;
}

/**
 * Called by the open trigger of the sheet
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Voting')
      .addItem('Stop Voting', 'stopVoting')
      .addItem('Start Voting', 'startVoting')
      .addToUi();
      
  gotoModeratorSheet();
}

function moveVoteResult(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
    
  // goto the input data
  var sheet = ss.getSheetByName("Form Responses 1" );
  ss.setActiveSheet(sheet);
  ss.duplicateActiveSheet();
  ss.renameActiveSheet(name);   
}

/**
 * get the voting subject from the moderator page 
 */
function getSubject() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getSheetByName("Moderator Page").getRange("A1:G40").getCell(1, 2); 
  
  return cell.getValue(); 
}

/**
 * Set the subject 
 */
function setSubject(text) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getSheetByName("Moderator Page").getRange("A1:G40").getCell(1, 2); 
  
  return cell.setValue(text); 
}

/** 
 * Get the final effort from the response sheet
 */
function getFinalEffort() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getSheetByName("Form Responses 1").getRange("A1:G40").getCell(2, 7); 
  
  return cell.getValue(text); 
}

/**
 * Set the final effort in the response sheet
 */
function setFinalEffort(text) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getSheetByName("Form Responses 1").getRange("A1:G40").getCell(2, 7); 
  
  return cell.setValue(text); 
}

/**
 * Stop voting by adding conclueded effort and saving the response page as a sheet
 * in the response spread sheet. 
 */
function stopVoting() {
  var ui = SpreadsheetApp.getUi(); // Same variations.
  var subject = getSubject();
     
  gotoModeratorSheet();
  
  if( getSubject() == "" ) {
    ui.alert("Voting subject not set");
    return;
  }
  
  if( isSheetExisting(subject) ) {
    ui.alert("voting result for " + subject + " already exists, please change the subject");
    return;
  }
    
  var result = ui.prompt(
      'Finish voting and add the final effort',
      'Final Effort:',
      ui.ButtonSet.OK_CANCEL);

  var button = result.getSelectedButton();
  
  switch( button ) {
    case ui.Button.OK:
      setFinalEffort(result.getResponseText());
      moveVoteResult(subject);
      setSubject("");
      break;
      
    case ui.Button.CANCEL, ui.Button.CLOSE: 
      break;
  }
}

/**
 * Start voting by clearing the repose sheet.
 */
function startVoting() {
  var ui = SpreadsheetApp.getUi(); // Same variations.
 
  gotoModeratorSheet();
  
  var result = ui.prompt(
      'Subject of voting',
      'JIRA Id:',
      ui.ButtonSet.OK_CANCEL);

  var button = result.getSelectedButton();
  
  switch( button ) {
    case ui.Button.OK:   
      setSubject( result.getResponseText() );
      // delete the old stuff from repomse sheet.
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Form Responses 1" );
      
      var thisMany = sheet.getDataRange().getNumRows() - 1;
      if( thisMany > 0 ) 
        sheet.deleteRows(2, thisMany);
      break;
      
    case ui.Button.CANCEL:
      ui.alert('I didn\'t get your name.');
      return;
      
    case ui.Button.CLOSE:
      ui.alert('You closed the dialog.');
      return;
  }
}