Solved: Bot to Update Database when Items are Added to a S... - Page 2

googlenoob62 · 12-12-2023 07:23 AM

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.

googlenoob62

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

View solution in original post

Bot to Update Database when Items are Added to a Sheet