OppScoreWeb/oppScoreGDriveWorkshop.js at main · jpcarrascal/OppScoreWeb · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
// Add functions to Google Sheets menu
function onOpen(e) {
  // Add a custom menu to the spreadsheet.
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
      .createMenu('PM Skills Workshop...')
      .addItem('Start data collection', 'createForm')
      .addItem('Finish data collection', 'resetForm')
      .addToUi();
}

// createForms() generates the form from a list of outcomes
// in a sheet called "Outcomes".
// Only put outcome statements in the sheet, nothing else
function createForm() {
  var date = Utilities.formatDate(new Date(), "GMT-8", "yyyy-MM-dd");
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var form = FormApp.create("PM Skills Workshop - " + date);
  form.setDestination(FormApp.DestinationType.SPREADSHEET, spreadsheet.getId());
  form.setShowLinkToRespondAgain(false);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Outcomes');
  //var data = sheet.getActiveRange().getValues();
  var data = sheet.getDataRange().getValues();
  var stepResponseArray = [];
  var outcomeResponseArray = [];
  var impsatResponseArray = [];
  for (var i = 0; i < data.length; i++)  {
    var step = data[i][0]
    var outcome = data[i][1]
    //var solution = data[i][2]
    var section = form.addPageBreakItem().setTitle(data[i][0]);
    var item = form.addScaleItem();
    item.setTitle("When you [" + step + "], how important is [" + outcome + "]?")
    .setBounds(1, 5).setLabels("Not at all important", "Extremely important").setRequired(true);

    var item = form.addScaleItem();
    item.setTitle("How mature is your organization on [" + outcome + "]?")
    .setBounds(1, 5).setLabels("Not at all mature", "Extremely mature").setRequired(true);
    stepResponseArray.push(step);
    stepResponseArray.push(step);
    outcomeResponseArray.push(outcome);
    outcomeResponseArray.push(outcome);
    impsatResponseArray.push("importance");
    impsatResponseArray.push("satisfaction");
  }

  var responsesSheet = ss.insertSheet('Responses');
  responsesSheet.appendRow(stepResponseArray);
  responsesSheet.appendRow(impsatResponseArray);
  responsesSheet.appendRow(outcomeResponseArray);

  Logger.log('Published URL: ' + form.getPublishedUrl());
  Logger.log('Editor URL: ' + form.getEditUrl());
  var url = form.getPublishedUrl();
  displayLink(url);
}

// resetForm() disconnects the form from the spreadsheet
// Only use after response collection is done.
// (before starting analysis)
function resetForm() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = ss.getSheets();
  var destination = ss.getSheetByName("Responses")
  var source = null;
  for (let sheet of sheets) {
    let sheetName = sheet.getName();
    let formUrl = sheet.getFormUrl();
    if (formUrl && sheetName.includes("Form responses")) {
      source = sheet;
      Logger.log("formid1 %s", FormApp.openByUrl(formUrl).getId());
    }
  }
  if(source !== null) {
    var sourceData = source.getRange(2,2,100,100);
    // get destination range
    var destinationData = destination.getRange("A4");
    // copy values to destination range
    sourceData.copyTo(destinationData);
  }
  var formURL = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  var form = FormApp.openByUrl(formURL);
  FormApp.openByUrl(formURL).removeDestination();
  var formID = form.getId();
  DriveApp.getFileById(formID).setTrashed(true);
  ss.deleteSheet(source);
  var date = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd hh:mm:ss");
  destination.setName("Responses " + date);
}

// displayLink() provides links to form for distribution
// - link to a QR code
// - link to form
function displayLink (url) {
  var qrapi = "https://api.qrserver.com/v1/create-qr-code/?size=800x800&data=";
  var html = '<html><body><a href="'+
              qrapi + url +
              '" target="blank">QR Code</a><br />' +
              '<a href="'+ url +
              '" target="blank">Form</a>' +
              '</body></html>';
  var ui = HtmlService.createHtmlOutput(html)
  SpreadsheetApp.getUi().showModelessDialog(ui,"Form Link");
}