Finally got it working with the following code is anyone wants to use this:
// Constants for Sheet
const CUSTOMER_NUMBER_COLUMN_INDEX = 6;
const HEADERS = ["Name", "Assignee", "Status", "Notes", "Customer", "Customer Record"];
// Constants for API
const APP_ID = "";
const TABLE_NAME = "Table_1";
const API_KEY = "";
// Base URL for AppSheet API
const BASE_URL = `https://api.appsheet.com/api/v2/apps/${APP_ID}/tables/${TABLE_NAME}/Action`;
// Triggered on edit in the sheet
function handleSheetEdit(e) {
const sheet = e.range.getSheet();
const range = e.range;
const customerNumber = getCustomerNumber(sheet, range);
if (!customerNumber) {
console.log("Invalid customer record");
return;
}
const data = getRowData(sheet, range);
try {
invokeApi(customerNumber, data);
} catch (error) {
console.log("Error in API call:", error.toString());
}
}
// Retrieve customer number from the edited row
function getCustomerNumber(sheet, range) {
const value = sheet.getRange(range.getRow(), CUSTOMER_NUMBER_COLUMN_INDEX).getValue()
return sheet.getRange(range.getRow(), CUSTOMER_NUMBER_COLUMN_INDEX).getValue();
}
// Retrieve data from the edited row
function getRowData(sheet, range) {
const values = sheet.getRange(range.getRow(), 1, 1, HEADERS.length).getValues()[0];
const data = {};
for (let i = 0; i < values.length; i++) {
data[HEADERS[i]] = values[i];
}
return data;
}
// API call to invoke AppSheet API for finding records
function invokeApi(customerNumber, data) {
// Constructing the body of the API request
const body = {
"Action": "Find",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time",
"Selector": `Filter(Customer Record, [Customer Record] = '${customerNumber}')`,
},
"Rows": []
};
console.log({"FIND: Customer Record": customerNumber});
const options = getApiOptions("post", body);
console.log("Find body:", options);
console.log("Find data:", data);
try {
const response = UrlFetchApp.fetch(BASE_URL, options);
const json = JSON.parse(response.getContentText());
const content = response.getContentText();
// If you want to parse it as JSON and then log
const jsonResponse = JSON.parse(content);
console.log(json.records)
if (json.records && json.records.length > 0) {
data["Customer Record"] = customerNumber;
updateRecord(data); // Record exists then just update fields in the App Sheet table
} else {
addRecord(data); // No customer record found then add to the App Sheet table
}
} catch (error) {
console.log("Error in API call:", error.toString());
}
}
// Update existing record
function updateRecord(data) {
const body = {
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time",
"UserSettings": {
"Option 1": "value1",
"Option 2": "value2"
}
},
"Rows": [JSON.stringify(data)]
};
const options = getApiOptions("post", body);
UrlFetchApp.fetch(BASE_URL, options);
}
// Add new record
function addRecord(data) {
const body = {
"Action": "Add",
"Properties": {},
"Rows": [data]
};
const options = getApiOptions("post", body);
UrlFetchApp.fetch(BASE_URL, options);
}
// Get API key and options for fetch
function getApiOptions(method, data = null) {
const options = {
"method": method,
"contentType": "application/json",
"headers": {
"ApplicationAccessKey": API_KEY
},
"muteHttpExceptions": true
};
if (data) {
options.payload = JSON.stringify(data);
}
return options;
}
