-
Notifications
You must be signed in to change notification settings - Fork 0
/
code.gs
93 lines (77 loc) · 3.42 KB
/
code.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
function doPost(e) {
var rawData = JSON.parse(e.postData.contents);
var data = rawData.data; // Your product data
var columnTitles = rawData.columnTitles; // Column titles
// Validate data structure
if (!Array.isArray(data) || !data.length || !Array.isArray(columnTitles)) {
return ContentService.createTextOutput(JSON.stringify({ "result": "error", "message": "Invalid data structure" }))
.setMimeType(ContentService.MimeType.JSON);
}
// Get sheet name for the week
var sheetName = getSheetNameForWeek(rawData.weekDate);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
// Create sheet if it doesn't exist
if (!sheet) {
sheet = ss.insertSheet(sheetName, 0);
sheet.appendRow(columnTitles);
}
if (rawData.delete === true) {
if (sheet) {
sheet.clear(); // Clear the contents of the sheet
return ContentService.createTextOutput(JSON.stringify({ "result": "cleared"}))
.setMimeType(ContentService.MimeType.JSON);
}
}
// Append data to the sheet
try {
var rowsToAppend = [];
var existingIdentifiers = getExistingIdentifiers(sheet);
for (var i = 0; i < data.length; i++) {
var row = data[i];
var identifier = row.ProductId + '-' + row.MunicipalityName;
if (!existingIdentifiers.has(identifier)) {
// Convert the object to an array of values
var rowArray = Object.values(row);
rowsToAppend.push(rowArray);
}
}
// Check if there are any rows to append
if (rowsToAppend.length > 0) {
// Append rowsToAppend to the sheet
var startRow = sheet.getLastRow() + 1; // Start appending after the last row
var numberOfColumns = rowsToAppend[0].length; // Number of columns to append
sheet.getRange(startRow, 1, rowsToAppend.length, numberOfColumns).setValues(rowsToAppend);
}
} catch (error) {
return ContentService.createTextOutput(JSON.stringify({ "result": "error", "message": error.message }))
.setMimeType(ContentService.MimeType.JSON);
}
return ContentService.createTextOutput(JSON.stringify({ "result": "success" }))
.setMimeType(ContentService.MimeType.JSON);
}
function getExistingIdentifiers(sheet) {
var existingData = sheet.getDataRange().getValues();
var identifiers = new Set();
var productIdIndex = 5; // Assuming ProductId is at index 5
var municipalityNameIndex = 34; // Assuming MunicipalityName is at index 34
// Starting from 1 to skip the header row
for (var i = 1; i < existingData.length; i++) {
var row = existingData[i];
var identifier = row[productIdIndex] + '-' + row[municipalityNameIndex];
identifiers.add(identifier);
}
return identifiers;
}
function getSheetNameForWeek(date) {
var start = new Date(date);
if (isNaN(start.getTime())) { // getTime() returns NaN for invalid dates
return date; // Return default date if input is invalid
}
start.setDate(start.getDate() - start.getDay() + 1); // Get Monday of the current week
var end = new Date(start);
end.setDate(end.getDate() + 6); // Get Sunday of the current week
return Utilities.formatDate(start, Session.getScriptTimeZone(), "MMM d") +
" - " +
Utilities.formatDate(end, Session.getScriptTimeZone(), "MMM d yyyy");
}