Hi everyone!
I will describe what for me is a bug.
I am introducing data (making edits) to some rows of the google sheet using an appscript.
That same google sheet is used as a table for an appsheet app.
Everything works fine up to here, the row is created in appsheet and in one of the automations there is a process that sends a Whatsapp through an appscript, then the message status is returned to the appscript and this same appscript updates the row with the message status using the apsheet api.
The problem is that the values on the message status column disappear when that row is edited in appsheet.
I’ve tried three different things to stop this from happening, first I check that the reset on edit functionality of the column was disabled. (It was disabled)
In second place I put ISBLANK ([_THIS]) on the edit condition for that column. (didn’t work)
In third place I protected the column in google sheets. (didn’t work)
Data introduced by appscript keep disappearing when I edit some column from appsheet.
Does anyone know about this happening in other cases, is there something that I can do to prevent data from disappearing?
Thank you in advance!
This is the appscript code that receives the status and enters it into google sheets:
function doPost(e) {
var data = JSON.parse(e.postData.contents);
count = Object.getOwnPropertyNames(data.entry[0].changes[0].value).length;
if ( count < 4 ) {
status = (data.entry[0].changes[0].value.statuses[0].status);
waId = (data.entry[0].changes[0].value.statuses[0].id);
const table = "%C3%93rdenes%20de%20trabajo";
var spreadsheetId = "1Zu...........................";
var sheetName = "Órdenes de trabajo";
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
var values = sheet.getDataRange().getValues();
if (values.filter(x => x[41] == waId)[0]) {
filter = values.filter(x => x[41] == waId);
var payload =
{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows":
[
{
"Key": filter[0][0],
"Message status": status
}
]
};
var options =
{
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"payload" : JSON.stringify(payload)
};
UrlFetchApp.fetch("https://api.appsheet.com/api/v2/apps/................./tables/"+table+"/Action?applicationAccessKey=........................", options);
} else { if (values.filter(x => x[45] == waId)[0]) {
filter = values.filter(x => x[45] == waId);
var payload =
{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows":
[
{
"Key": filter[0][0],
"Message status archivo confirmado": status
}
]
};
var options =
{
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"payload" : JSON.stringify(payload)
};
UrlFetchApp.fetch("https://api.appsheet.com/api/v2/....................../tables/"+table+"/Action?applicationAccessKey=..............................", options);
};
};
} else {
confirmacion = (data.entry[0].changes[0].value.messages[0].button.text);
waId = (data.entry[0].changes[0].value.messages[0].context.id);
const table = "%C3%93rdenes%20de%20trabajo";
var spreadsheetId = "1Zu...............................";
var sheetName = "Órdenes de trabajo";
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
var values = sheet.getDataRange().getValues();
if (values.filter(x => x[41] == waId)[0]) {
filter = values.filter(x => x[41] == waId);
var payload =
{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows":
[
{
"Key":filter[0][0],
"Confirmación de recepción": confirmacion,
"Message status": "read"
}
]
};
var options =
{
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"payload" : JSON.stringify(payload)
};
UrlFetchApp.fetch("https://api.appsheet.com/api/v2/apps/........................./tables/"+table+"/Action?applicationAccessKey=...........................", options);
} else { if (values.filter(x => x[45] == waId)[0]) {
filter = values.filter(x => x[45] == waId);
var payload =
{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows":
[
{
"Key":filter[0][0],
"Confirmación de recepción archivo confirmado": confirmacion,
"Message status archivo confirmado": "read"
}
]
};
var options =
{
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"payload" : JSON.stringify(payload)
};
UrlFetchApp.fetch("https://api.appsheet.com/api/v2/apps/........................../tables/"+table+"/Action?applicationAccessKey=...........................", options);
};
};
};
};
