shared-expenses-manager/FormManager.js at main · nightmaregaurav/shared-expenses-manager · 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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
function createAndGetForm () {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rawSheet = ss.getSheetByName("Raw");
  if(rawSheet){
    throw new Error("Comment this line if you know what are doing!");
    var rawFormUrl = rawSheet.getFormUrl();
    if (rawFormUrl){
      FormApp.openByUrl(rawFormUrl).removeDestination();
    }
    ss.deleteSheet(rawSheet);
  }

  const spreadsheetFile = DriveApp.getFileById(ss.getId())
  const spreadsheetFileName = spreadsheetFile.getName();
  form = FormApp.create(spreadsheetFileName);
  form.setDescription("Form to record shared expenses so we can figure out who owes whom and how much.")
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());

  SpreadsheetApp.flush();

  const formUrl = form.getEditUrl().replace("edit", "viewform");
  const formSheet = ss.getSheets().find(s => s.getFormUrl() == formUrl);
  if (formSheet) {
    formSheet.setName("Raw");
    formSheet.getRange("G1").setValue("Settled?");
  }

  const formFile = DriveApp.getFileById(form.getId())
  const parentFolder = spreadsheetFile.getParents().next();
  formFile.moveTo(parentFolder);

  return form;
}

function getForm () {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rawSheet = ss.getSheetByName("Raw");
  if(!rawSheet){
    return;
  }

  const rows = rawSheet.getRange("A2:A").getValues();
  rows.forEach((row, rowIndex) => {
    if (row[0] !== "") {
      rawSheet.getRange(`G${rowIndex+2}`).setDataValidation(SpreadsheetApp.newDataValidation().requireCheckbox().build());
    }
  });

  const formUrl = rawSheet.getFormUrl();
  if(!formUrl){
    return;
  }

  return FormApp.openByUrl(formUrl);
}

function addFormElements(form) {
  const existingFormItems = form.getItems();

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let participantsSheet = ss.getSheetByName("Participants");
  if(!participantsSheet) {
    participantsSheet = ss.insertSheet("Participants");
  }

  const firstColumn = participantsSheet.getRange("A2:A").getValues();
  const participants = firstColumn.map(row => row[0]).filter(cell => cell !== "");
  if (participants.length === 0){
    participants.push("please");
    participants.push("add");
    participants.push("participants name");
    participants.push("in the");
    participants.push("participants");
    participants.push("sheet");
  }

  createOrUpdateWhoField(form, existingFormItems, participants);
  createOrUpdateWhyField(existingFormItems, form);
  createOrUpdateHowMuchField(existingFormItems, form);
  createOrUpdateSplitAmongField(existingFormItems, form, participants);
  createOrUpdateWhenField(existingFormItems, form);
}

function createOrUpdateSplitAmongField(existingFormItems, form, participants) {
  const splitAmongFieldTitle = "Split Among?";
  const splitAmongFieldDescription = "Who should be equally liable? (Expense will be splitted among selected participants only)";

  let splitAmongField = existingFormItems.find(item => item.getTitle() === splitAmongFieldTitle)?.asCheckboxItem();
  if (!splitAmongField) {
    splitAmongField = form.addCheckboxItem();
    splitAmongField.setTitle(splitAmongFieldTitle);
  }

  splitAmongField.setChoiceValues(participants);
  splitAmongField.setRequired(true);
  splitAmongField.setHelpText(splitAmongFieldDescription);
  splitAmongField.setValidation(FormApp.createCheckboxValidation().requireSelectAtLeast(1).build());
}

function createOrUpdateHowMuchField(existingFormItems, form) {
  const howMuchFieldTitle = "How Much?";
  const howMuchFieldDescription = "How much was spent?";

  let howMuchField = existingFormItems.find(item => item.getTitle() === howMuchFieldTitle)?.asTextItem();
  if (!howMuchField) {
    howMuchField = form.addTextItem();
    howMuchField.setTitle(howMuchFieldTitle);
  }

  howMuchField.setRequired(true);
  howMuchField.setHelpText(howMuchFieldDescription);
  howMuchField.setValidation(FormApp.createTextValidation().requireNumberGreaterThan(0).build());
}

function createOrUpdateWhyField(existingFormItems, form) {
  const whyFieldTitle = "Why?";
  const whyFieldDescription = "What was the expense for?";

  let whyField = existingFormItems.find(item => item.getTitle() === whyFieldTitle)?.asTextItem();
  if (!whyField) {
    whyField = form.addTextItem();
    whyField.setTitle(whyFieldTitle);
  }

  whyField.setRequired(true);
  whyField.setHelpText(whyFieldDescription);
}

function createOrUpdateWhenField(existingFormItems, form) {
  const whenFieldTitle = "When?";
  const whenFieldDescription = "When did this happen?";

  let whenField = existingFormItems.find(item => item.getTitle() === whenFieldTitle)?.asDateItem();
  if (!whenField) {
    whenField = form.addDateItem();
    whenField.setTitle(whenFieldTitle);
  }

  whenField.setRequired(true);
  whenField.setHelpText(whenFieldDescription);
}

function createOrUpdateWhoField(form, existingFormItems, participants) {
  const whoFieldTitle = "Who Spent?";
  const whoFieldDescription = "Who spent for shared purpose?";

  let whoField = existingFormItems.find(item => item.getTitle() === whoFieldTitle)?.asListItem();
  if (!whoField) {
    whoField = form.addListItem();
    whoField.setTitle(whoFieldTitle);
  }

  whoField.setChoiceValues(participants);
  whoField.setRequired(true);
  whoField.setHelpText(whoFieldDescription);
}