Help! API upsert not working on timed trigger

Hey folks, I seem to have run into a really weird issue.

I have a Google app script that syncs a Bamboo HR report with a coda table. It uses the upsert function from the coda API to make sure it only adds new instances of the report, using a unique key.

The script works just fine if I run it manually, and doesn’t add any duplicates.

But when the script runs on a timed trigger, it adds new rows.

I would really appreciate any help or insight :pray:t4:

Weird - I don’t feel like have enough knowledge to answer that question without seeing code / more context.

But general question, why use an apps script over building a pack for Bamboo?

@Scott_Collier-Weir – thank you so much for being willing to help! I have added the script below. It is really bizarrer that is upserts it when run manually, but then has duplicates when it runs on a trigger.

The reason I went with the script is because my skill level is more get a template and edit, rather than being able to create code from scratch. This felt more doable :slight_smile:

// @ts-nocheck
// Replace the BAMBOO_HR_API_KEY placeholder with your actual Bamboo HR API key
const BAMBOO_HR_API_KEY = 'INSERY KEY';

// Replace the CODA_API_KEY placeholder with your actual Coda API key
const CODA_API_KEY = 'INSERT KEY';

// Replace the CODA_DOC_ID placeholder with the actual id of the Bamboo HR table in Coda.io
const CODA_DOC_ID = 'DOC ID';

// Replace the CODA_TABLE_ID placeholder with the actual id of the Bamboo HR table in Coda.io
const CODA_TABLE_ID = 'TABLE ID';

//Make sure to update the url and report number for the Bamboo HR Report in line 42
//Coda's library for Google Apps Script: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

//CODA column IDs
const CODA_COL_ID_UniqueRow = 'ADD COL ID';
const CODA_COL_ID_EmployeeNum = 'ADD COL ID';
const CODA_COL_ID_BambooID = 'ADD COL ID';
const CODA_COL_ID_Status = 'ADD COL ID';
const CODA_COL_ID_FirstName = 'ADD COL ID';
const CODA_COL_ID_LastName = 'ADD COL ID';
const CODA_COL_ID_Compensation_Date = 'ADD COL ID';
const CODA_COL_ID_Pay_Schedule = 'ADD COL ID';
const CODA_COL_ID_Pay_Type = 'ADD COL ID';
const CODA_COL_ID_Pay_rate = 'ADD COL ID';
const CODA_COL_ID_Paid_per = 'ADD COL ID';
const CODA_COL_ID_Comp_Change_Reason = 'ADD COL ID';
const CODA_COL_ID_Comp_Change_Comment = 'ADD COL ID';

function syncBambooHRToCoda() {
  // Use the GET method to get data from Bamboo HR
  const options = {
    method: 'GET',
    headers: {
      Accept: 'application/json',
      authorization: 'Basic ' + BAMBOO_HR_API_KEY
    }
    
  };

  const response = UrlFetchApp.fetch(
    'ADD URL',
    options
  );
  

  const data1 = JSON.parse(response.getContentText());
  const employees = data1["employees"];

   

  CodaAPI.authenticate(CODA_API_KEY);

  Logger.log('CODA auth complete');

  var loopCtr = 0;
  var body = {
    rows: [],
    keyColumns: [
        CODA_COL_ID_UniqueRow
      ]
  }

  // Iterate through the list of employees, add unique row ID, add row to body
  employees.every(employee => {
    var uniqueRowID = employee['payRateEffectiveDate']+"-"+employee['id'];
    var bambooHRID = employee['id'];
    var employeeNumber = employee['employeeNumber'];
    var status = employee['status'];
    var firstName = employee['firstName'];
    var lastName = employee['lastName'];
    var compensationDate = employee['payRateEffectiveDate'];
    var paySchedule = employee['paySchedule'];
    var payType = employee['payType'];
    var payRate = employee['payRate'];
    var payPer = employee['payPer'];
    var payChangeReason = employee['payChangeReason'];
    var compensationComments = employee['4045'];

    //if (loopCtr > 300)
    //{
      //return false;
    //}

    if (uniqueRowID === null)
    {
      Logger.log('Null Unique Row ID! Run away!')
      uniqueRowID = '';
    }
     if (bambooHRID === null)
    {
      Logger.log('Null bambooHRID! Run away!')
      bambooHRID = '';
    }
    if (employeeNumber === null)
    {
      Logger.log('Null employee number! Run away!')
      employeeNumber = '';
    }

        if (status === null)
    {
      Logger.log("status is null for #" + employeeNumber);
      status = '';
    }


    if (firstName === null)
    {
      Logger.log("firstName is null for #" + employeeNumber);
      firstName = '';
    }

    if (lastName === null)
    {
      Logger.log("lastName is null for #" + employeeNumber);
      lastName = '';
    }

    if (compensationDate === null)
    {
      Logger.log("gender is null for #" + employeeNumber);
      compensationDate = '';
    }

    if (paySchedule === null)
    {
      //Logger.log("paySchedule is null for #" + employeeNumber);
      paySchedule = '';
    }

    if (payType === null)
    {
      //Logger.log("payType is null for #" + employeeNumber);
      payType = '';
    }

    if (payRate === null)
    {
      Logger.log("payRate is null for #" + employeeNumber);
      payRate = '';
    }

    if (payPer === null)
    {
      //Logger.log("payPer is null for #" + employeeNumber);
      payPer = '';
    }

    if (payChangeReason === null)
    {
      //Logger.log("payChangeReason is null for #" + employeeNumber);
      payChangeReason = '';
    }

    if (compensationComments === null)
    {
      compensationComments = '';
    }

    var empRow = {
          cells: [
            {
            'column': CODA_COL_ID_UniqueRow, 
            'value': uniqueRowID
            },
            {
            'column': CODA_COL_ID_BambooID, 
            'value': bambooHRID
            },
            {
            'column': CODA_COL_ID_EmployeeNum, 
            'value': employeeNumber
            },
            {
              'column': CODA_COL_ID_Status,
              'value': status
            },
            {
            'column': CODA_COL_ID_FirstName,
              'value': firstName
            },
            {
              'column': CODA_COL_ID_LastName,
              'value': lastName
            },
            {
              'column': CODA_COL_ID_Compensation_Date,
              'value': compensationDate
            },
            {
              'column': CODA_COL_ID_Pay_Schedule,
              'value': paySchedule
            },
            {
              'column': CODA_COL_ID_Pay_Type,
              'value': payType
            },
            {
              'column': CODA_COL_ID_Pay_rate,
              'value': payRate
            },

            {
              'column': CODA_COL_ID_Paid_per,
              'value': payPer
            },
            {
              'column': CODA_COL_ID_Comp_Change_Reason,
              'value': payChangeReason
            },
            {
              'column': CODA_COL_ID_Comp_Change_Comment,
              'value': compensationComments
            }
            
          ]
        };

        body.rows[loopCtr] = empRow;
        loopCtr++;


    return true;

  
    });

    Logger.log(loopCtr + ' Employee rows added');

    CodaAPI.upsertRows(CODA_DOC_ID, CODA_TABLE_ID, body);
    Logger.log('Coda updated');

  };
function myfunction(){
syncBambooHRToCoda();
};

Thanks! I’ll take a look soon

I’ve also been interested in developing a Bamboo HR pack myself - DM me and we could discuss details! Depending on what you need, and if I can get access to your account for testing purposes I could likely develop the pack

A pack would be much more stable and user friendly than a script

A coda pack would be amazing! I am using a client’s key, but Bamboo HR has a free trial that can be used and I would be happy to help with testing.

I figured out what was going wrong with the script above and I think its a bug at Coda’s end.

I was using a string for the unique identifier, and for some reason Coda was not evaluating the same string values as equal/duplicates consistently. We changed it to numbers and so far it seems to have gone back to doing upserts (only adding new rows instead of adding duplicates).

A few other things I learned the hard way working with the Bamboo HR API:

  • They have a data field called ‘id’ which is better to use as the unique identifier than the client designated ‘employeeNumber’ (employee ID). For data that is a subtable (example job history) to employee data, they often use the ‘id’ as the linkage and ommit the employeeNumber (employee ID).
  • Its probably easier to build a more consistent connection with their employee directory since it has standard fields.
  • We needed more specific title and salary data so we used the GET method that syncs with custom tables. Still pretty cool, but required fairly precise and time consuming mapping of the columns… guessing that’s because of my very limiting toolkit :slight_smile:

Hope this helps others who run into this challenge!
-A

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.