Spreadsheet formula

Hi All,

I am using a spreadsheet formula in two sheets. In one it works great, in the other it doesn’t. The formula is to calculate the driving KM between two adresses. When i use this formula in a sheet where i make a new entry it works fine. But i have another case:

With google script i pull new data from a CMR system every 15 min. The JSON data is filled in the google sheet columns. (transport information) I want to use this spreadsheet formula in combination with appsheet workflow JSON PUT request to update the KM in my CMR system. But when i update the row, my spreadsheet formula doesn’t work. As far as i can see the formula is not entert.

So long story short:

  1. Formula works when i make a new entry
  2. Formula does not work when i update a row

Can anyone help?

-Remco

@Remco_Edelenbos Primary thing to consider here seems to be calculating the distance in your app definition with a virtual column, instead of in your data source table (if you have coordinates available). It’s ideal to have no formulas/expressions in your app data source tables, they likely will not sync well with your app.

1 Like

Hi Peter,

Yes that is true. I only have a adres ( from the CMR system ) I do not have the formula in the sheet. I just put the formula in the field spreadsheet formula in Appsheet columns.

Is it posible?

You’re leaving out a lot of details here.

How does the google script pull new data?

How is the JSON data Filled into the sheet columns? Are existing cells overwritten? Are new rows or columns added? Are existing rows or columns deleted? Do key column values change?

Which “spreadsheet formula” do yo want to use? One already in your spreadsheet? Or do you want AppSheet to add a spreadsheet formula?

You want an AppSheet workflow to prompt your CMR system to update the KM?

Hi Steve,

How does the google script pull new data?

function APPTRANSPORT() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheet = ss.getSheetByName(“Transportregels”)
var url = “https://11111.rest.afas.online/XXXXXskip=-1&take=-1&orderbyfieldids=-Volgnummer”;
var headers = {
“Authorization”: "Token "+ Utilities.base64Encode(1XXXXXXXXXX)
};

var options = {
“method” : “get”,
“headers” : headers
};

var response = UrlFetchApp.fetch(url, options); // get feed
var dataAll = JSON.parse(response.getContentText());
var dataRows = dataAll[‘rows’];

var rowHeaders = Object.keys(dataRows[0]);
var rows = [rowHeaders]; // Retrieve headers.
for (var i = 0; i < dataRows.length; i++) {
var rowData = ;
for (var j = 0; j < rowHeaders.length; j++) {
rowData.push(dataRows[i][rowHeaders[j]]); // Retrieve values.
}
rows.push(rowData);
}

sheet.getRange(1,1,rows.length,rows[0].length).setValues(rows);
// Put values to Spreadsheet.
}

How is the JSON data Filled into the sheet columns? Are existing cells overwritten? Are new rows or columns added? Are existing rows or columns deleted? Do key column values change?
Yes everything is overwritten. The key value is the Transportnumber, so this is unique every time. So if there is a new transport (the google script updates every 15min), it pulls all the transport rows with the filter criteria (the filter i made on the CMR side)

Which “spreadsheet formula” do yo want to use? One already in your spreadsheet? Or do you want AppSheet to add a spreadsheet formula?
=IFERROR(roundup(drivingkm($T2,$U2),0),"")
Or in RC:
=IFERROR(roundup(drivingkm(RC21,RC20),0),"")
This is not in my sheet because its get overwritten. So i want appsheet tho make the formula. The drivingkm part of the fomula is done by google script as well. The T2 an U2 are adresses.

You want an AppSheet workflow to prompt your CMR system to update the KM?
Yes, so there is a new transport, that needs to trigger the workflow to make a JSON post to the CMR system (thats works great on other part of the app). Then when the google script pulls the transport list after 15min the KM are now filled in.

I hope this is the information you guys need.

Thank you for the additional details!

If I am interpreting your interest correctly, you want AppSheet to insert the IFERROR() spreadsheet formula into your sheet after your script has updated the sheet. If my interpretation is correct, AppSheet cannot do what you want: AppSheet does not and cannot respond to changes made to your spreadsheet outside of AppSheet.

Beyond that, I feel your needs are beyond my area of expertise. Perhaps @LeventK might have additional insight to share?

@Remco_Edelenbos, you could try writing an onChange() function in your GAS that will run after an update has occurred in that sheet and repopulates the cell with the spreadsheet formula.

1 Like