Bot to Update Database when Items are Added to a Sheet

[googlenoob62]

I'm trying to build out some automation for when  data is added to a Sheet it takes that information and updates/adds to an existing  Database with the relevant information. 

It should use the Customer Number as the key to either update is that key exists in the Database and if it doesn't exist then it should add it to the database.

In the example data below customer 127895 should be added to the database with all the fields and customer 963852 should update the fields

I havent been able to figure out how to build a bot to do this. It seems you can only trigger off a change but not taken an action to update a database from that action.

[Landan_QREW]

This seems like a good use case for Apps Script. You could write an Apps Script that sends data to your AppSheet App to update the database.

[googlenoob62]

I started down the Apps Script path after the suggestion and have what I think should work. When I make the update to the sheet it shows that the script runs fine but nothing is actually updated in the AppSheet Database. Any ideas what needs to be changed or that I missed:

// Constants for API and Sheet
const CUSTOMER_NUMBER_COLUMN_INDEX = 6;
const HEADERS = ["Name", "Assignee", "Status", "Notes", "Customer", "Customer Number"]; 
const VIEW_ID = ""; 
const API_KEY_PROPERTY_NAME = "";
const BASE_URL = "https://api.appsheet.com/api/v1/views/" + VIEW_ID + "/records"; 

// Function triggered on edit in the sheet
function handleSheetEdit(e) {
  var sheet = e.range.getSheet();
  var range = e.range;

  // Check if the edit is in the customer number column
  if (range.getColumn() !== CUSTOMER_NUMBER_COLUMN_INDEX) {
    return;
  }

  var customerNumber = getCustomerNumber(sheet, range);
  if (!customerNumber) {
    Logger.log("Invalid customer number");
    return;
  }

  var data = getRowData(sheet, range);

  try {
    var existingRecord = getRecord(customerNumber);
    existingRecord ? updateRecord(customerNumber, data) : createRecord(data);
  } catch (error) {
    Logger.log('Error in API call: ' + error.toString());
  }
}

// Retrieve customer number from the edited row
function getCustomerNumber(sheet, range) {
  return sheet.getRange(range.getRow(), CUSTOMER_NUMBER_COLUMN_INDEX).getValue();
}

// Retrieve data from the edited row
function getRowData(sheet, range) {
  var values = sheet.getRange(range.getRow(), 1, 1, HEADERS.length).getValues()[0];
  var data = {};
  for (var i = 0; i < values.length; i++) {
    data[HEADERS[i]] = values[i];
  }
  return data;
}

// Check if a record exists
function getRecord(customerNumber) {
  var url = BASE_URL + "?filter=CUSTOMER_NUMBER=" + encodeURIComponent(customerNumber);
  var response = UrlFetchApp.fetch(url, getApiOptions("get"));
  var data = JSON.parse(response.getContentText());
  return data.records.length > 0 ? data.records[0] : null;
}

// Update an existing record
function updateRecord(customerNumber, data) {
  var url = BASE_URL + "/" + encodeURIComponent(customerNumber);
  UrlFetchApp.fetch(url, getApiOptions("put", data));
}

// Create a new record
function createRecord(data) {
  UrlFetchApp.fetch(BASE_URL, getApiOptions("post", data));
}

// Get API key and options for fetch
function getApiOptions(method, data = null) {
  var options = {
    "method": method,
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + getApiKey()
    }
  };
  if (data) options["payload"] = JSON.stringify(data);
  return options;
}

// Retrieve the API key from script properties
function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty(API_KEY_PROPERTY_NAME);
}

[Landan_QREW]

Do you have the api enabled in your appsheet app?

[googlenoob62]

I have the API enabled in the app:

Getting this error though when the script runs which I'm not sure what else I need to do since its the same account that is used to run the script that has access to the App Sheet:

Info

Error in API call: Exception: Request failed for https://api.appsheet.com returned code 404

[Landan_QREW]

It may be advantageous to throw an example in Postman and see if it goes through. It may be easier to troubleshoot the API specific issues that way.

[googlenoob62]

After working through this I have a script that will create the record on the App Sheet Table when an edit is made to the Google Sheet. I can't however get the Update call to work. If there are ideas why it doesn't work open for suggestions:

// Constants for Sheet
const CUSTOMER_NUMBER_COLUMN_INDEX = 6; 
const HEADERS = ["Name", "Assignee", "Status", "Notes", "Customer", "Customer Record"]; 
const APP_ID = ""; 
const TABLE_NAME = ""; 
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;


  if (range.getColumn() !== CUSTOMER_NUMBER_COLUMN_INDEX) {
    return;
  }

  const customerNumber = getCustomerNumber(sheet, range);
  if (!customerNumber) {
    Logger.log("Invalid customer record");
    return;
  }

  const data = getRowData(sheet, range);
  try {
    invokeApi(customerNumber, data);
  } catch (error) {
    Logger.log("Error in API call:", error.toString());
  }
}

// Retrieve customer record from the edited row
function getCustomerNumber(sheet, range) {
  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 for finding records
function invokeApi(customerNumber, data) {
  const body = {
    "Action": "Find",
    "Properties": {},
    "Rows": [{"Customer Record": customerNumber}]
  };

  const options = getApiOptions("post", body);

  try {
    const response = UrlFetchApp.fetch(BASE_URL, options);
    const json = JSON.parse(response.getContentText());

    if (json.records && json.records.length > 0) {
      data["Customer Record"] = customerNumber;
      updateRecord(data); // Record exists then just update fields in teh App Sheet table
    } else {
      addRecord(data); // No customer record found then add to the App Sheet table
    }
  } catch (error) {
    Logger.log("Error in API call:", error.toString());
  }
}

// Update existing record
function updateRecord(data) {
  const body = {
    "Action": "Edit",
    "Properties": {},
    "Rows": [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;
}

[Landan_QREW]

Can you post the result of a console.log(body) in your function: 

function updateRecord(data) {
  const body = {
    "Action": "Edit",
    "Properties": {},
    "Rows": [data]
  };
}

  That may assist in debugging

[googlenoob62 · Accepted solution]

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;
}

[AleksiAlkio]

Just FYI.. this is also possible to do without any script if you use the AppSheet's Events AddOn. When the AddOn is in use, it's able to monitor changes on your gSheet and trigger the Bot. The Bot can then create a new row to a table as you can read values from the row you just added or modified via gSheet.

[Landan_QREW]

@AleksiAlkio - Thanks for posting this. I knew this was a feature in Beta a while back, but I could not remember where it was accessed.

[mateo]

Hi @googlenoob62,
I think I had a similar issue once and the reason was blank "Properties" in my JSON payload. It looks like yours is blank too. I mean this part here:

const body = {
    "Action": "Add",
    "Properties": {}, 
    "Rows": [data]
  };

Hope it helps 😊