It's been several days since I'm stuck on this problem. I've tried everything I saw but I think I miss some logic of the Google Script.
I have a php form which send data on post to a Google Spreadsheet. It uses a Google Script to handle the request and update a Google Spreasheet. The problem is that I added one ID per row (generated on my web app and sent through my php form) but I can't find the way to compare the ID received on my Google Script in order to compare it to the ID of the last row of my spreadsheet. The goal is to replace the row if the ID already exists or add the new row at the end of my spreadsheet.
Here is the Google script I've found on the internet which works but does not handle row updates based on the ID (The commented lines are the code I've tried to add but because of the fact that I cannot manage to log data when I'm sending my php form, I cannot visualize what I'm missing.)
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
function intialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
// var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
// var lastRow = sheet.getLastRow();
// var lastRowId = values[lastRow - 1][0];
// var test = JSON.parse(e.postData.contents);
// Logger.log(test);
// if (test.id == lastRowId) {
// return;
// }
var newRow = headers.map(function(header) {
return header === 'timestamp' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow}))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}```
function lfunko(obj) {
const [hA, ...arr] = "obj.2d";
const idx = {};
hA.forEach((h, i) => { idx[h] = i; });
let idA = arr.map(r => r[idx["ID"]]);
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("SheetName");
const [h, ...vs] = sh.getDataRange().getValues();
let idx1 = {};
h.forEach((h, i) => { idx1[h] = 1; })
vo = []
vs.forEach((r, i) => {
let n = idA.indexOf(idx1["ID"])
if (!~n) {
vo.push(r)
} else {
vo.push(arr[n])
}
})
Logger.log(JSON.stringify(vo));
sh.getRange(1,1,vo.length,vo[0].length).setValues(vo);
}