Request Assistance Converting Spreadsheet Formulas to AppSheet Formulas.

[jdtyler-dev]

Issue

I've been struggling with performance issues due to using a spreadsheet formula that makes an API call to the NHTSA database to retrieve VIN information on a vehicle and populates in the app to provide techs in the field with vin decoding services used in automotive diagnostics, invoice document generation, as well as automated email to our clients.

The current system is working as designed but I did not account for the cost of processing using spreadsheet formulas opposed to virtual columns and AppSheet formulas.

Everything works fine until {{VIN}} reaches about [50] rows or so then the {{VIN}} bottlenecks and the API formulas populate {{CELL}} with "Loading..." on random rows of {{VIN}} causing blank output in the app due to every time one of our (8) technicians are actively syncing the app, multiple times a day, the spreadsheet formula is calling out (50)  VIN#'s every sync and if (3) techs sync at the same time that's (150) api calls at once being made to the API causing cells in {{VIN}} not populate or display the Return Response in the Sheet or the App.

I setup a bot to delete finalized tickets once a week to maintain the sheets to stay around (50) rows but when the lines are deleted from {{SERVICE}} it deletes the {{CELL}} formulas as well and the AutoFill feature creates a referencing nightmare due to it not updating the cell references to append from to the new order of the rows.

Example: 

1. Rows [ (14), (15), (16) ] in {{SERVICE}} are set to a status of "Finalized".
2. On Saturday the Maintenance Bot uploads the and appends the finalized rows to BigQuery and Deletes them from {{SERVICE}}.
3. When Rows are Deleted from {{SERVICE}} it Deletes the Formula from {{CELL}} as well then moves remaining Rows up.
4. Results: Row (17) is in Row (14), | Row (18) is in Row (15) | Row (19) is in Row (16) | etc.
5. Autofill does not account for the Update on the Rows and the {{CELL}} Reference in the formula is now set to  Row (14) but the formula is referencing Row (17) of {{VIN}} for information.
6. This causes Errors to occur and the Vehicle Information does not to populate in the app forcing me to not utilize the maintenance automation and manually update and archive the tables weekly costing me time needed on other projects.

Solution

Find an App Script function that can be called to accommodate the API Call and Return the .csv values from the API response by appending the results to [VIN] and using AppSheet reference formulas to display in the app.

Architectural Insight

I use a mirror column to keep {{VIN}} in sync with  {{SERVICE}} so that any time a row is deleted or finalized and/or archived it will remove the row from {{SERVICE}} and the row with matching [_row_number] from {{VIN}}.

[START]

EVENT: A VIN Barcode is scanned in the app using form and Submitted

PROCESS:

1. Append the Scanned VIN# Barcode Value to {{SERVICE}} 
2. Format the VIN# if more than 17 Characters
3. Append formatted VIN# to a function variable in {{VIN}} using as a request Parameter in the API request URL
4. Call the api using the VIN# Variable from {{SERVICE}} as a Parameter in the Host URL
5. Append the response to {{VIN}}
6. FROM {{VIN}} append [Year], [Make], and [Model] {{CELL}} values from the recorded API response to {{Service}} which displays in the app to the Technician.

Any help or direction on converting, and/or provisioning, an economical workaround would be greatly appreciated.

API Call To NHTSA Formula : 

Spreadsheet

=IF(NOT(ISBLANK(${T}!${C}), QUERY(IMPORTDATA("https://vpic.nhtsa.dot.gov/api/vehicles/decodevinvalues/" & ${T}!${C} & "?format=csv"), "SELECT * OFFSET 1", 0), "") 

AppSheet

IF(NOT(ISBLANK(${T}!RC[8])), QUERY(IMPORTDATA("https://vpic.nhtsa.dot.gov/api/vehicles/decodevinvalues/" & ${T}!RC[8] & "?format=csv"), "SELECT * OFFSET 1", 0), "")

Ordered ID Array Formula on ADDS/UPDATES/DELETES in Service Log):

Spreadsheet

=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(NOT(ISBLANK(${T}!${C})), ROW(${C})-1, "")), 1, 1)

AppSheet

=ArrayFormula(IF(NOT(ISBLANK(${T}!RC[9])), ROW(RC)-1, ""))

Appending Vehicle and Id information from VIN Log -> Service Log on ADD:

Spreadsheet

=IF(NOT(ISBLANK(${T}!${C})),CELL("CONTENTS", ${T}!${C}), "")

AppSheet

APPENDVINFO(VINDecoder!RC[-7]),//[VID] (Ref)
APPENDVINFO(VINDecoder!RC[-7]),//[VinID] (Number)
APPENDVINFO(VINDecoder!RC[16]),//[Year] (Number)
APPENDVINFO(VINDecoder!RC[12]),//[Make] (Text)
APPENDVINFO(VINDecoder!RC[13])//[Model] (Text)

[Aurelien · Accepted solution]

---

@jdtyler-dev wrote:

WOW nothing? man i figured at least one person would take a crack at it

---

Your question is too long to read 😀

You may want to simplify it and focus on one step at a time. We all volunteers in helping here, and that means we want to understand quickly and help you quickly as well.

I didn't took the time to read it thoroughly, but basically, here is what I can tell:

1) if you are using ARRAYFORMULA is your Sheets ==> forget about it and use AppSheet expression indeed. You may benefit from using virtual columns in some cases.

For reference:

Use virtual columns - AppSheet Help

Expressions: The Essentials - AppSheet Help

2) You may have to consider changing your data structure if you have too many complex expressions.

3) for query expression, you may benefit from using Slices. This is the AppSheet equivalent of a SQL View.

For reference:Slices: The Essentials - AppSheet Help

4) When regenerating a table structure from a datasource on Google Sheets, formula expressions are written automatically in the Formula field. Normally you don't have to figure a way to translate an expression from Sheets to AppSheet if you use formulas in every cell of your Sheets. AppSheet recognizes it, and copy it the same way using RC references.

EDIT: please remember I didn't take time to read your whole post thoroughly, my apologies in advance.

For consideration, you may want to read this:  Guide on How To Create a Community Post - Google Cloud Community

[scott192 · Accepted solution]

Have you told your bot that your appscript function is returning an Object or a Text?

If it's an object, then you will get the value for each cell by using [decodeVIN].[ObjectValue] where [ObjectValue] will be one of the headers in your returned object.

{Fieldname1:"A value",
Fieldname2:"A value",
Fieldname3:"A value"...}

[scott192 · Accepted solution]

In your Appscript that returns a CSV list back to you, do the following

Convert the CSV to an array.

myArray = csvtext.split(",")

Then place the array values into an object which is named accordingly to their contents.

const appsheetobject = {fieldname1:myArray[0],fieldname2:myArray[1],fieldname3:myArray[2]}

finally at the end of your script, return the object you have constructed from the csv array

return appsheetobject;

In Appsheet you can then get those values by using your [decodeVIN].[fieldname1] etc 

[scott192 · Accepted solution]

That's great!

So your text string is now delimitted with a comma space so you can split like this

const myValues = myString.split(", ")

You will then be able to build your object

const returnToAppsheet = {fieldname1:myValues[0],fieldname2:myValues[1]...etc}

Then your final line of script to pass back to appsheet

return returnToAppsheet

All as before...and hopefully....you can pick those values up and write them to your VIN table and trigger other bots to do various things.

[jdtyler-dev]

WOW nothing? man i figured at least one person would take a crack at it

[Steve]

You should probably hire someone to help. I'm certainly not going to invest my own time trying to understand your complex problem.

[jdtyler-dev]

All I was asking was for a formula conversion. I can solve the problems just was looking for verbage. I know you got fired but you dont need to be rude.  Besides I see people all the time struggling with VIN decoding and intended to post on tips and tricks so not like I had selfish intent. 

[Marc_Dillon]

You should probably hire someone to help. I'm certainly not going to invest my own time trying to understand your complex problem

---

As someone who could potentially be hired to solve something like that, that's exactly what I was contemplating to say here as well. How you thought it was rude though is beyond me.

[jdtyler-dev]

"I'm certainly not going to invest my own time trying to understand your complex problem" comes off as a little rude considering all i was attempting was provide all the information. Its not that complex. As someone who i probably would have hired its not a great selling point to take sides against the individual that would do the hiring, but thanks for the suggestion and thoroughly putting a sour taste in the appsheet community when i was simply fishing for thoughts, a little insight, not to get teamed up on, and basically made to feel unwelcome and as though I am in some kind of fault when all i did was ask for a simple formula conversion and provided necessary information up front rather than initiating a lengthy back and forth. If i offended you or anyone with my query i do apologize and i will look elsewhere from now on.

[jdtyler-dev]

Also i would like to iterate that the length of my post was previously brought to my attention and i responded respectfully in reciprocation of the courteous advice given in regards to segmenting my question and in the future I intended on doing as such, so to continue to add on to that advice in the manner it was delivered was rude and I dont think its in good form to do so on a help forum.  

[Aurelien · Accepted solution]

---

@jdtyler-dev wrote:

WOW nothing? man i figured at least one person would take a crack at it

---

Your question is too long to read 😀

You may want to simplify it and focus on one step at a time. We all volunteers in helping here, and that means we want to understand quickly and help you quickly as well.

I didn't took the time to read it thoroughly, but basically, here is what I can tell:

1) if you are using ARRAYFORMULA is your Sheets ==> forget about it and use AppSheet expression indeed. You may benefit from using virtual columns in some cases.

For reference:

Use virtual columns - AppSheet Help

Expressions: The Essentials - AppSheet Help

2) You may have to consider changing your data structure if you have too many complex expressions.

3) for query expression, you may benefit from using Slices. This is the AppSheet equivalent of a SQL View.

For reference:Slices: The Essentials - AppSheet Help

4) When regenerating a table structure from a datasource on Google Sheets, formula expressions are written automatically in the Formula field. Normally you don't have to figure a way to translate an expression from Sheets to AppSheet if you use formulas in every cell of your Sheets. AppSheet recognizes it, and copy it the same way using RC references.

EDIT: please remember I didn't take time to read your whole post thoroughly, my apologies in advance.

For consideration, you may want to read this:  Guide on How To Create a Community Post - Google Cloud Community

[jdtyler-dev]

well normally i wouldn't be so lengthy with my post but this is a pretty complex system and has alot of moving parts so i figured the best way to get a working solution was provide you guys with all the info one might need to figure it out. i will try to partition my questions in the future.

[jdtyler-dev]

i would just like to say thank you for your constructive advise and the manner it was communicated. i also wanted to apologize if my post was overwhelming or caused any friction on the forum. thank you for your input and helpful advice.

[scott192]

I scanned your request and would like you to clarify something.

Am I correct in assuming that everytime a new row is added to VIN (When the barcode is scanned by the user in the app) that your special formula recalculates every single row that was ever added and fetches the information again even though that information was populated the first time the code was scanned?

[jdtyler-dev]

So when scanned it actually gets loaded to the service log and then formatted and the formatted vin is then used as a variable and copied over to the vin log. from there it makes the api call using the vin variable. after which it takes the year make and model and posts it back to the service log and displays it in the app and allows the user to click a button showing them the full details of the results if they need more info than just the year make and model of the vehicle.

[jdtyler-dev]

and yes it scans at every sync due to the fact i'm using spreadsheet formulas opposed to formulas in appsheet. that's how it processes spreadsheet formulas and its causing a cache issue keeping it from loading and keeping the tech from doing his job without being able to build the service id without the year make and model.

[jdtyler-dev]

basically at 50 rows its does this. we are projecting to having closer to 200 rows consistently so you can see how this becomes a high priority issue. im thinking of using a 3rd party addon to perform the call and parse to the sheet removing the formula or getting someone to right an app script i can call to automate from appsheet. would be easier if i could find a way to translate the spreadsheet formula into an appsheet syntax and then i would have a lot more flexibility and use bots

[Aurelien]

Hi @jdtyler-dev 

At reading previous posts here, I think there was some kind of human reaction to a written text, that is by nature subject to interpretation. No offense to anyone 🙂

I have the same opinion than @Steve's and @Marc_Dillon 's about hiring someone for doing a specific job.

The way I see it, as your limit is about API quota, is to call this function with a bot, get the return value and write it with the next step of the process within the same bot.

If you wish to do it by yourself, you may want to use the getRequest() function, which documentation is here: Class UrlFetchApp  |  Apps Script  |  Google Developers

You may need to process the data returned, but at least you can give it a try.

Some informations about how to use it is here:

Call Apps Script from an automation - AppSheet Help

Use return values from Apps Script tasks - AppSheet Help

[jdtyler-dev]

I was able to successfully writ an appscript that triggers when the [VIN] culumn has a changr data event occur, then successfully makes the call using the value of column [VIN] passed as a parameter when AppSheet calls the script, then JSON.parse.Results[0].${COLUMN} It parses all the response items and returns them as a comma seperated list and I can use the return feature to provide [decode].[output] to pull the list into AppSheet but now I cant figure out how to distribute them into their cells in the row. It just buts the whole list into the first cell containing [decodeVIN].[output]

[jdtyler-dev]

Should I use a virtual column to recieve the list and from there use a formula to itemize and disperse across the row? If so any thoughts on a performance friendly formula to split the list and recognize which cell to apply to based on the comma separation. 

[scott192 · Accepted solution]

In your Appscript that returns a CSV list back to you, do the following

Convert the CSV to an array.

myArray = csvtext.split(",")

Then place the array values into an object which is named accordingly to their contents.

const appsheetobject = {fieldname1:myArray[0],fieldname2:myArray[1],fieldname3:myArray[2]}

finally at the end of your script, return the object you have constructed from the csv array

return appsheetobject;

In Appsheet you can then get those values by using your [decodeVIN].[fieldname1] etc 

[Aurelien]

Did you try to set the output column with type EnumList?

This way, you can use:

INDEX([outputColumn],1)
or
INDEX(SPLIT([outputColumn],","),1)

 You can call a data change action to split your numerous output values with a single action triggered by the bot.

EDIT: updated the INDEX expression

[jdtyler-dev]

So if the return of the App Script  produces this

I would setup a Bot in AppSheet with the following configuration to allocate the return values to the desired cells?

EVENT
1. event = adds (with no conditions)

PROCESS
2. Run A Task (call a script)
- add my script
- select my function
- set my vin parameter
- set return value to true
- set the return to be of type EnumList,

- put in all the object keys as list values,
- set to auto compute and do not allow other values.

2. Setup Data Change Action
set values of this row
  column = [makeid] (the start position to parse the values) ,
   formula =

INDEX(SPLIT([decodeVIN].[Output], ","), 1)

Or would i set the return as an object and just reference all the Object Keys in the return value?

I apologize for the length of my response i just need to ensure im setting this up correctly. Patience and understanding is greatly appreciated.

[scott192]

I was angling more towards you returning an object and referencing the Object Keys?  To be honest, I have only ever used Objects when returning multiple values and didn't even consider there was an Array option. 

Your screenshot suggests that you have the object all ready and set to go now.  If you used that in your bot you can easily place the specific values in your columns using the [Stepname].[fieldname] suggestion.

Go for it!  It will work 😁

[jdtyler-dev · Accepted solution]

SUCCESS!!! 

was super tedious adding formulas for 2 x 121 return values in appsheet but it worked. one set to define the return object and one set to add data to row after return was received but IT WORKED! app is running super fast, no spreadsheet formulas used, THANK every1 that took the time to assist me on this despite its complex nature. I intend on adding collaborators section of the tips and tricks guide i will be making shortly to show how to create integrate a free, process friendly, fully automated vin decoder in your appsheet application. My way of giving back to the community that helped me through this learning experience.

[scott192]

Oh wow!  I really had no idea you were grabbing that many from the vin decoder!  I thought about 10 from your screenshots 😂

Congratulations though!  All of that upfront work has speeded up your app AND you discovered (like me) how incredibly useful Appscript Tasks with return variables is!  Kudos to the appsheet team @nico for making that feature happen!

[jdtyler-dev]

yeah i was up until 3 am last night before it finally populated. talk about a sigh of relief and although short lived the best sleep ive had all week. hopefully this guide im working on will allow other citizen developers some insight into using appsheet and appscript together to push the limits of no-code and provide a better resource than the only other available guide that uses spreadsheet formulas and bottlenecks at 50 rows....

[scott192]

Ahh yes...I think that bottlenect is actually a documented limitation of the QUERY formula in sheets?  I think you had placed it into an ARRAYFORMULA expression and for each row in the array it was behaving as a separate QUERY.  There is a limit of 50 QUERY expression per sheet I think?

[jdtyler-dev]

that would explain the cause of the constant internal error issues. i reached out to google days ago regarding this and still have yet to hear back from them. glad i was able to get ahead of the curve on this instead of waiting for a response. thx again. you were a huge help and if there is any way i can compensate for it just let me know.

[scott192]

That's very kind of you and I have also learned lots from many people in this community.  I have found that trying to describe a problem is often more difficult than what the solution turns out to be 😀

Looking forward to seeing your vindecode scripts at a later date!

[nico]

Thanks for highlighting this use case & helping with a solution. Yes, this highlights the need for some type of "bulk expression" editor to make it easier to add dozens of expressions at once but glad it was helpful for this case!

[Aurelien]

Congrats !!!!

[jdtyler-dev]

Successful workflow automation for decoding a VIN # using:  

• Appsheet
• Sheets
• Appscript

Multiple successful executions of the app script function being called from appsheet with a decode vin request.

example of the response data properly parsing into the sheet from appscript

 continued

example of successful result of the data decoded from the vin parsing to its parent service sheet to which the user can interact and view the return data.

final result in appsheet of the return data cleaned up and made interactive for the user using UI actions

( please note all the VIN numbers displayed throughout this post were randomly generated using https://randomvin.com/ ) 
This setup also works with the barcode scanner and using the REF column as a parameter in app script.

[scott192 · Accepted solution]

Have you told your bot that your appscript function is returning an Object or a Text?

If it's an object, then you will get the value for each cell by using [decodeVIN].[ObjectValue] where [ObjectValue] will be one of the headers in your returned object.

{Fieldname1:"A value",
Fieldname2:"A value",
Fieldname3:"A value"...}

[jdtyler-dev]

so ill have to set return as object in AppSheet and assign the data type for each on of these field correct? seems tedious but at this point ill do whatever is needed to get this to function properly. at current, the issue i'm facing is causing downtime for our techs which is the exact opposite of the purpose of the app. i just want to thank you guys for the help provided.

[jdtyler-dev]

could i possibly format the return like this and utilize formatting the return to appsheet syntax?

[scott192]

So, I did not realise you were returning so many columns back to your app 😕

Every item in the object has to be of the same datatype to work in Appsheet...will this cause you problems?

[jdtyler-dev]

no i already set all values top text type in appsheet so to accommodate the return type

[scott192]

So, I noticed in your returned CSV that some of the addresses have commas in them...that's going to mess with the split() command and cause mismatched columns.  Does the VIN return API allow you to select a different delimitter?

[jdtyler-dev]

i think i can wrap the value to constrain the result into the whatever symbol i can wrap it with like [] or {} ()

[jdtyler-dev]

would something like this help

or 

[scott192]

Oh no, I didn't mean that.

Your [makeid]variable is a long string of CSV values.  The arrayofvalues = makeid.split(",")part would create an indexed array of all the values that are in that text string...however, if you look carefully you can see that the vehicle manufacturer Ford has an address that has a comma in it.

FORD MOTOR COMPANY OF CANADA, LTD

That comma will screw up the split and one of the array elements will be FORD MOTOR COMPANY OF CANADA and the following element will be LTD which will bugger up the counting through the elements to get the exact ones that you want to return in the object.

So, my question was is it possible that the CSV for [makeid] could be separated with a different symbol?  Maybe an @ or a %...does the API that returns the VIN info have any options in that regard?

[jdtyler-dev]

i was able to remove parts of the return that do not apply to my use case

ex(motorcycle info, bus info, trailer info, manufacturer [which removed the addresses with " , " in the value], gvwr [removed the weight value array with " , "in the value], etc.) and now i am getting a return without " , " in the response.