ContactSheet_to_GoogleContacts/CSV.gs at master · aplater/ContactSheet_to_GoogleContacts · 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
function CSVConvert() {

  var ss = SpreadsheetApp.getActive();
  var main = ss.getSheetByName("Main List");
  var csv = ss.getSheetByName("CSV Format");

  csv.getRange("A2:CK").clearContent();

  var data = main.getDataRange().getValues();

  for (m = 1; m < data.length; m++) {

    var rowArr = [];

    //name

    var nameArr = data[m][0].split(" ");

    if (nameArr.length == 1) {

      rowArr.push(nameArr[0], "", "");

    }

    if (nameArr.length == 2) {

      rowArr.push(nameArr[0], "", nameArr[1]);

    }

    if (nameArr.length == 3) {

      rowArr.push(nameArr[0], nameArr[1], nameArr[2]);

    }

    var groupArr = ["My Contacts;"]

    for (g = 7; g < data[m].length; g++) {

      var HoYCheck = data[m][g].split(" ");

      if (data[m][g] != "" && HoYCheck[0] != "HoY") {

        groupArr.push(data[m][g] + ";");

      }

      if (HoYCheck[0] == "HoY") {

        groupArr.push("HoY;");

      }

    }

    rowArr.push("", "", "", "", "", "", "", "", "", "", "", data[m][3], data[m][4], "", data[m][1], "", "", data[m][2], "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", data[m][1], "", "", "IES Älvsjö", data[m][5], data[m][7],
               "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", groupArr.join(""))

    csv.appendRow(rowArr);

  }

  saveAsCSV();

}

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csv = ss.getSheetByName("CSV Format");
  var range = csv.getRange("A1:CJ" + csv.getLastRow());
  var vals = range.getValues();
  var csvString = vals.join("\n");

  var files = DriveApp.getFolderById("1uoJX7na-_vNF_-s2r4oIak68w4hTO1H1").getFiles();

  while (files.hasNext()) {
    var file = files.next();

    file.setTrashed(true);

  }


  DriveApp.getFolderById("1uoJX7na-_vNF_-s2r4oIak68w4hTO1H1").createFile(ss.getName() + ".csv", csvString);

  for (r = 1; r < vals.length; r++) {

    var groups = vals[r][87].split(";");

    for (g = 0; g < groups.length; g++) {

      if (groups[g] == "My Contacts") {

        groups.splice(g, 1, "System Group: My Contacts");

      }

    }

    csv.getRange("CJ" + (r + 1)).setValue(groups.join(";"));

  }

}