1. /**
2.  * @OnlyCurrentDoc
3.  */
4.  
5. function Refresh(e){
6.   // Check if key code was changed
7.   if (e.range.getValue() == SpreadsheetApp.getActiveSpreadsheet().getRange("KeyCode").getValue()) {
8.     Fetch({}, true);
9.   // Check if refresh interval setting was changed
10.   } else if (e.range.getValue() == SpreadsheetApp.getActiveSpreadsheet().getRange("RefreshSetting").getValue()) {
11.     CreateTrigger();
12.   }
13. }
14.  
15. function RefreshButton(e){
16.   Fetch({}, false, true);
17. }
18.  
19. function Fetch(triggerInfo, new_key, buttonClicked, attempt) {
20.   // Check if key was changed and set Loading message
21.   new_key = new_key || false;
22.   buttonClicked = buttonClicked || false;
23.   var sheet = SpreadsheetApp.getActiveSpreadsheet();
24.   var old_timestamp = sheet.getRange("raw_data!A1").getValue();
25.   var key = sheet.getRange("KeyCode").getValue();
26.   var attempt = attempt || 1;
27.  
28.   // If there is a key, fetch the csv data
29.   if (key.length > 0) {    
30.     var options =
31.         {
32.           // Ensure a fresh copy of the csv file is fetched every time.
33.           headers : {'Cache-Control' : 'max-age=0', 'Content-Type' : 'application/json' }
34.         };
35.     try {
36.       var response = UrlFetchApp.fetch("https://data.wowaudit.com/shadowlands/" + key + ".json", options).getContentText();
37.     } catch(e) {
38.       if (attempt > 3) {
39.         var response = false;
40.       } else {
41.         return Fetch(triggerInfo, new_key, buttonClicked, attempt + 1);
42.       }
43.     }
44.    
45.     // Only parse the data if the request returned data
46.     if (response) {
47.       sheet.getRange("raw_data!A1").setValue("Loading...");
48.       var json = JSON.parse(response);
49.       var data = [];
50.       var row_data = [];
51.       for (var row = 0; row < json.length; row++ ) {
52.         row_data=[];
53.         for (var column = 0; column < json[0].length; column++ ){
54.           row_data.push(json[row][column]);
55.         }
56.         data.push(row_data);
57.       }
58.      
59.       sheet.getRange("raw_data!A1").offset(0, 0 ,json.length, json[0].length).setValues(data);
60.       sheet.getRange("raw_data!A1").offset(json.length, 0, 102, json[0].length).clearContent();
61.      
62.       // Don't update row visibility if the data is refreshed through the button,
63.       // As it's possible that the person who clicked doesn't have access to the sheet.
64.       if (!buttonClicked) {
65.         UpdateRowVisibility(sheet, json.length);
66.       }
67.      
68.     } else if (new_key) {
69.       // Wipe everything if there is no data and the key was changed
70.       sheet.getRange("raw_data!A1").offset(0, 0, 102, 300).clearContent();
71.       UpdateRowVisibility(sheet, 20);
72.     } else {
73.       // Keep old data if there is no data but the key wasn't changed
74.       sheet.getRange("raw_data!A1").setValue(old_timestamp);
75.     }
76.   } else {
77.    // Wipe everything if there is no key set and then set the initial IMPORTDATA function
78.    sheet.getRange("raw_data!A1").offset(0, 0, 102, 300).clearContent();
79.    sheet.getRange("raw_data!A1").setValue("=IMPORTDATA(CONCATENATE(\"https://data.wowaudit.com/shadowlands/\",KeyCode,\".csv\"))");
80.   }
81. }
82.  
83. function UpdateRowVisibility(sheet, rows) {
84.   // Update the amount of rows shown after the data has refreshed
85.   if (rows < 20) { rows = 20; }
86.  
87.   // First, show all rows
88.   sheet.getSheetByName('Summary').showRows(1, 110);
89.   sheet.getSheetByName('Overview & Rankings').showRows(1, 102);
90.   sheet.getSheetByName('Great Vault & Gear').showRows(1, 102);
91.   sheet.getSheetByName('Raids').showRows(1, 105);
92.  
93.   // Then, hide unnecessary rows
94.   sheet.getSheetByName('Summary').hideRows(8 + rows, 101 - rows);
95.   sheet.getSheetByName('Overview & Rankings').hideRows(2 + rows, 101 - rows);
96.   sheet.getSheetByName('Great Vault & Gear').hideRows(2 + rows, 101 - rows);
97.   sheet.getSheetByName('Raids').hideRows(4 + rows, 101 - rows);
98. }
99.  
100. function CreateTrigger() {
101.   // First delete old triggers
102.   var allTriggers = ScriptApp.getProjectTriggers();
103.   for (var i = 0; i < allTriggers.length; i++) {
104.     ScriptApp.deleteTrigger(allTriggers[i]);
105.   }
106.  
107.   // Then create new triggers
108.   sheet = SpreadsheetApp.getActiveSpreadsheet();
109.   var interval = sheet.getRange("RefreshSetting").getValue().slice(0,2);
110.   ScriptApp.newTrigger("Fetch").timeBased().everyMinutes(interval).create();
111.   ScriptApp.newTrigger("Refresh").forSpreadsheet(sheet).onEdit().create();
112.  
113.   // Then give user feedback of success
114.   if (ScriptApp.getProjectTriggers().length == 2) {
115.     sheet.getRange('Settings!B9').setValue('The sheet is automatically refreshing!');
116.     sheet.getRange('Settings!B9').setBackground('#b6d7a8');
117.     sheet.getRange('Settings!B6').setValue('');
118.   } else {
119.     sheet.getRange('Settings!B9').setValue('Click here to start automatic refreshing!');
120.     sheet.getRange('Settings!B9').setBackground('#ea9999');
121.     sheet.getRange('Settings!B6').setValue('=IF(Guild="","",SetupIncompleteWarning)');
122.   }
123.  
124.   // Finally, refresh the sheet to replace the initial IMPORTDATA function
125.   Fetch();
126. }