APP Script

function doGet(e) {
// Set excel Id
// Excel field: timestamp, date, subject, summary, income, expenditure
var excelId = “1qa-GS0OIEHQuluqUobZOFl2s9JHvfW7mdpzxowShR1M”

// Get the parameters
// For save account info parameters : pDate, pSubject, pSummary, pIncome, pExpenditure
// For read account info parameters : isRead=Y(must have), maxRow:read max rows(default=10)
var params = e.parameter;
// Set sheet
var SpreadSheet = SpreadsheetApp.openById(excelId);

var Sheet = SpreadSheet.getSheetByName(“account”);
var LastRow = Sheet.getLastRow();

var isRead = params.isRead;

if(isRead == null || isRead.toUpperCase() != “Y”) {
var pTimeMark = Utilities.formatDate(new Date(), “GMT+8”, “yyyy-MM-dd HH:mm:ss”);
var pDate = params.pDate;
var pSubject = params.pSubject;
var pSummary = params.pSummary;
var pIncome = params.pIncome;
var pExpenditure = params.pExpenditure;

// Save account data
Sheet.getRange(LastRow+1, 1).setValue(pTimeMark);
Sheet.getRange(LastRow+1, 2).setValue(pDate);
Sheet.getRange(LastRow+1, 3).setValue(pSubject);
Sheet.getRange(LastRow+1, 4).setValue(pSummary);
Sheet.getRange(LastRow+1, 5).setValue(pIncome);
Sheet.getRange(LastRow+1, 6).setValue(pExpenditure);

// Return 0 – OK
return ContentService.createTextOutput(“0”);
} else if(LastRow < 2) {
// Return No Data
return ContentService.createTextOutput(“No Data”);
} else {
var dMark = “\””;
var szMaxRow = params.maxRow;
var maxRow = 10;
if(szMaxRow != null && parseInt(szMaxRow) != NaN && parseInt(szMaxRow) > 0){
maxRow = parseInt(szMaxRow);
}
var startColumn = 2;
var cntColumn = 5
var startRow = LastRow – maxRow + 1;
var cntRow = maxRow;
if (startRow < 2){
startRow = 2;
cntRow = LastRow – 1;
}
var szJson = “[“;
var szRow = “”;
var range = Sheet.getRange(startRow, startColumn, cntRow, cntColumn);
var values = range.getValues();

for (var row in values) {
szRow = “{“;
rowId = parseInt(row) + startRow;
for (var col in values[row]) {
colId = parseInt(col) + 1;
if(col == 0){
szRow = szRow + dMark + “col_0” + dMark + “:” + dMark + rowId + dMark;
var objDateString = values[row][col];
var szDate = objDateString;
var timestamp = Date.parse(objDateString);
if (isNaN(timestamp) == false) {
var objDate=new Date(objDateString);
szDate = objDate.getFullYear() + “-” + (objDate.getMonth() + 1) + “-” + objDate.getDate();
}
szRow = szRow + “,” + dMark + “col_” + colId + dMark + “:” + dMark + szDate + dMark;
} else {
szRow = szRow + “,” + dMark + “col_” + colId + dMark + “:” + dMark + values[row][col] + dMark;
}
}
szRow = szRow + “}”
if(row == 0){
szJson = szJson + szRow;
} else {
szJson = szJson + “,” + szRow;
}
}
szJson = szJson + “]”;
// Return result by json format data
return ContentService.createTextOutput(szJson);
}
}