-
Notifications
You must be signed in to change notification settings - Fork 0
/
Phonebook.gs
178 lines (143 loc) · 4.79 KB
/
Phonebook.gs
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
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
class Phonebook {
constructor(sheetInstance = null) {
this.phoneNumberDict = {};
if (sheetInstance) {
this.fillPhoneBook(sheetInstance);
}
}
/**
* Method fills the phonebook with pairs of (PHONE_NUMBER, NAME)
* from given Google Sheets sheet. The pairs of values must be
* in the first and second column respectively.
*/
fillPhoneBook(phonebookSheet) {
if (!phonebookSheet) {
throw `Invalid phonebook sheet ${phonebookSheet}.`
}
let data = phonebookSheet.getDataRange().getValues();
data.forEach((row) => this.addPhoneNumber(row[0], row[1]));
}
/**
* Method adds a single phone number to the phonebook.
*/
addPhoneNumber(phoneNumber, name) {
if (typeof phoneNumber === 'number' && typeof name === 'string') {
this.phoneNumberDict[phoneNumber] = name;
}
}
/**
* Method replaces all of the phone numbers in first and second
* column of the target sheet with names from the phone book.
*/
replacePhoneNumbers(targetSheet) {
if (!targetSheet) {
throw `Invalid target sheet ${targetSheet}.`;
}
let range = targetSheet.getDataRange();
let values = range.getValues();
for (let i = 0; i < values.length; i++) {
values[i][0] = this.replace(values[i][0]);
values[i][1] = this.replace(values[i][1]);
}
range.setValues(values);
}
/**
* Method replaces a single phone number entry with correspoinding
* name in the phonebook if possible.
*/
replace(number) {
if (this.phoneNumberDict[number]) {
return this.phoneNumberDict[number];
}
else {
return number;
}
}
/**
* Method serialized Phonebook object to a JSON string.
*/
toJSON() {
return JSON.stringify(this.phoneNumberDict);
}
/**
* Method deserializes Phonebook object from a JSON string.
*/
fromJSON(jsonString) {
this.phoneNumberDict = jsonString;
}
}
/*******************************************************************************
* Save/Load Phonebook Between Calls
********************************************************************************/
/**
* Function sets the script property 'selectedPhoneBook' to provided
* phonebook. Used to store the phone book between script calls.
*/
function setSelectedPhonebook(phonebook) {
let scriptProperties = PropertiesService.getScriptProperties();
if (phonebook != EMPTY_PHONEBOOK) {
scriptProperties.setProperty('selectedPhoneBook', phonebook.toJSON());
}
else {
scriptProperties.setProperty('selectedPhoneBook', EMPTY_PHONEBOOK);
}
}
/**
* Function returns the selected phonebook if one was created.
* Otherwise it returns EMPTY_PHONEBOOK value.
*/
function getSelectedPhonebook() {
let scriptProperties = PropertiesService.getScriptProperties();
let selectedPhoneBookValue = scriptProperties.getProperty('selectedPhoneBook');
if (selectedPhoneBookValue != EMPTY_PHONEBOOK) {
let phonebook = new Phonebook();
phonebook.fromJSON(JSON.parse(selectedPhoneBookValue));
return phonebook;
}
else {
return EMPTY_PHONEBOOK;
}
}
/*******************************************************************************
* Google Sheets UI
********************************************************************************/
/**
* Reserved name function - called when Google Sheets are opened.
* Create a new menu button on the toolbar for NoPixel Phonebook.
*/
function onOpen() {
SpreadsheetApp.getUi().createMenu("NoPixel Phonebook")
.addItem("Select Phonebook", "selectPhonebook")
.addItem("Replace Phone Numbers", "replacePhoneNumbers")
.addToUi();
setSelectedPhonebook(EMPTY_PHONEBOOK);
}
const EMPTY_PHONEBOOK = -1;
function selectPhonebook() {
let ui = SpreadsheetApp.getUi();
let phonebookSheetName = ui.prompt("Enter phonebook sheet name.").getResponseText();
let sheet = SpreadsheetApp.getActive().getSheetByName(phonebookSheetName);
if (sheet == null) {
setSelectedPhonebook(EMPTY_PHONEBOOK);
ui.alert(`InvalidSheetName: Sheet with name ${phonebookSheetName} was not found.`);
}
else{
setSelectedPhonebook(new Phonebook(sheet));
ui.alert(`Phonebook "${phonebookSheetName}" was created successfully.`);
}
}
function replacePhoneNumbers() {
let ui = SpreadsheetApp.getUi();
let targetSheetName = ui.prompt("Enter target sheet name.").getResponseText();
let sheet = SpreadsheetApp.getActive().getSheetByName(targetSheetName);
if (sheet == null) {
ui.alert(`InvalidSheetName: Sheet with name ${targetSheetName} was not found.`);
}
else if (getSelectedPhonebook() == EMPTY_PHONEBOOK){
ui.alert("EmptyPhonebookError: Phonebook is currently empty, please select one of the sheets as phonebook.");
}
else{
let phonebook = getSelectedPhonebook();
phonebook.replacePhoneNumbers(sheet);
}
}