ReplicateETFSheets/IndexHandler.gs at main · ProgramComputer/ReplicateETFSheets · 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

functionwriteCSVDataToSheet(data){
varname=data.getBlob().getName()
varcontents=Utilities.parseCsv(data);
varss=SpreadsheetApp.getActiveSpreadsheet();
varitt=ss.getSheetByName(name+' Holdings')
itt.clear();
if(!itt){

ss.insertSheet(name+' Holdings');
}

ss.getSheetByName(name+' Holdings').getRange(1,1,contents.length,contents[0].length).setValues(contents);
returnss.getSheetByName(name+' Holdings').getName();
}

//add parameters for custom urls such as isCsv and url
functiongetHoldings(){
varurl=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Create New Orders").getRange("F2").getValue()


varresponse=UrlFetchApp.fetch(url);


varsheetName=response.getBlob().getName();
if(response.getBlob().getName().match(/csv/)!=null){
writeCSVDataToSheet(response);
SpreadsheetApp.getActive().toast("The CSV file was successfully imported into "+sheetName+".","⚠️ Alert");

}
elseif(response.getBlob().getName().match(/xlsx/)!=null){
writeXLSVDataToSheet(response)
SpreadsheetApp.getActive().toast("The XLSV file was successfully imported into "+sheetName+".","⚠️ Alert");
}
else
{
SpreadsheetApp.getActive().toast("The CSV file was unsuccussfully imported.","⚠️ Alert");

}

returnresponse.getBlob().getName()
}


functionwriteXLSVDataToSheet(data){


constexcelFile=data.getBlob();


letconfig={
title: excelFile.getName(),
parent: "Direct Indexing",
mimeType: MimeType.GOOGLE_SHEETS
};
letspreadsheet=Drive.Files.insert(config,excelFile);
varsource=SpreadsheetApp.openById(spreadsheet.getId()).getActiveSheet();
varitt=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(excelFile.getName()+" Holdings")
itt.clear()
if(itt){

SpreadsheetApp.getActiveSpreadsheet().deleteSheet(itt);
}
constdestination=SpreadsheetApp.getActiveSpreadsheet();

constnewSheet=source.copyTo(destination)
newSheet.setName(excelFile.getName()+" Holdings")
Drive.Files.trash(spreadsheet.id)
}