Request help Converting formula from Sheets syntax to AppSheet syntax

[jdtyler-dev]

Reference

** I have removed the table and cell references to maintain proprietary security **

{{SERVICE}} or ${T} : Service  Logging Table

{{VIN}} or ${T}: VIN Logging Table

{{CELL}} or ${C}: Referenced Cell 

Concern

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.

Cause #1

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.

Cause #2

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.

Correction #1

Convert the formulas from the sheet over to AppSheet to gain event, process, and action control features and would also reduce bottleneck effect on performance reducing sync time and dev support.  

Correction #2

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.

Request

I've been trying to convert a known working formula from Google Sheets formula syntax to perform the same action using AppSheet formula syntax but i cant seem to get the correct expression method combination correct.

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 : 

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

AppSheet Translation:

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), "")

Requesting Sheets => AppSheet Formula Conversion

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

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

AppSheet Translation:

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

Requesting Sheets => AppSheet Formula Conversion

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

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

// Service Table columns using append formulas

• [VID] = (H2:H100)
• [VinID] = (I2:I100)
• [Year] = (M2:M100)
• [Make] = (N2:N100)
• [Model] = (O2:O100)

// VIN Table appended reference columns 

• [ID] = (A2:A100)
• [VIN_ID] = (B2:B100)
• [modelyear] = (AC2:AC100)
• [make] = (Z2:Z100)
• [model] = (AB2:AB100) 

//  Appended entity relation 

• [VID] <= [ID]
• [VinID] <= [VIN_ID]
• [Year] <= [modelyear] 
• [Make] <= [make]
• [Model] <= [model]

AppSheet Translation ( Service Table  <=  VIN Table 😞

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)

@Stefan_QREW 

[jdtyler-dev]

Apologies. I apparently was in tips & tricks when i should have posted in QA. Here is the link to this post in QA 

Request Assistance Converting Spreadsheet Formulas... - Google Cloud Community