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.

[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]

so would the output im looking for be like this?