-
Notifications
You must be signed in to change notification settings - Fork 10
/
google_sheets_monday_integration.gs
83 lines (71 loc) · 2.7 KB
/
google_sheets_monday_integration.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
// API key (for illustration only, you should store production key in an environment variable)
var mondayAPIkey = "YOUR_API_KEY_HERE";
// ID of destination board
var boardID = 392204356; // change this :)
// list that stores the column IDs of the monday.com columns to be updated (ordered left to right)
var colMappings = ["name", "text", "text0", "status9", "date3"]; // change this :)
function makeAPICall(key, query, variables) {
var url = "https://api.monday.com/v2";
var options = {
"method" : "post",
"headers" : {
"Authorization" : key,
},
"payload" : JSON.stringify({
"query" : query,
"variables" : variables
}),
"contentType" : "application/json"
};
var response = UrlFetchApp.fetch(url, options);
Logger.log("API results: " + response.getContentText());
return response;
}
function getPulseID(key, pulseName) {
var query = "query($board:Int!, $name:String!){items_by_column_values(board_id:$board, column_id:\"name\", column_value:$name){id}}";
var variables = {
"board" : boardID,
"name" : pulseName
};
var data = makeAPICall(key, query, variables);
Logger.log(data.getContentText());
return data;
}
function updateCell(e) {
// declare pulse ID based on column 6
var rowNumber = e.range.getRow();
var name = e.range.getSheet().getSheetValues(rowNumber,1,1,1)[0][0];
var pulseID = e.range.getSheet().getSheetValues(rowNumber,6,1,1);
// get pulse ID if column 6 is empty
if (pulseID == 0) {
Logger.log("No pulse ID given.");
pulseID = JSON.parse(getPulseID(mondayAPIkey, name)).data.items_by_column_values[0];
// if item doesn't exist, create it
if (typeof pulseID == "undefined") {
var query = "mutation($board:Int!, $name:String!){create_item(board_id:$board, item_name:$name){id}}";
var variables = {
"board" : boardID,
"name" : name
};
pulseID = JSON.parse(makeAPICall(mondayAPIkey, query, variables)).data.create_item.id;
}
else {
pulseID = pulseID.id;
}
Logger.log("Pulse ID returned was " + pulseID);
e.range.getSheet().getRange(rowNumber, 6).setValue(pulseID);
}
// update columns
for (var i = 0; i < colMappings.length; i++) {
var cellValue = String(e.range.getSheet().getSheetValues(rowNumber,(i+1),1,1)[0][0]);
query = "mutation($item:Int!, $board:Int!, $val:JSON!, $col:String!){change_column_value(item_id:$item, board_id:$board, column_id:$col, value:$val){id}}";
variables = {
"item" : parseInt(pulseID),
"board" : boardID,
"col" : colMappings[i],
"val" : JSON.stringify(cellValue)
};
var res = JSON.parse(makeAPICall(mondayAPIkey, query, variables));
}
Logger.log(res.getContentText());
}