In Part 1 of this series (Batch Process Records SpreadsheetWeb API Power Query), we demonstrated how to connect Excel to the SpreadsheetWeb API using Power Query to send a single request and retrieve calculated results from an Excel-based web application.
But what happens when you need to process multiple records at once?
In real-world scenarios—especially in insurance quoting and underwriting workflows—you often have to run the same pricing model for dozens, even hundreds, of customer records. Manually triggering single API calls for each input row quickly becomes inefficient and time-consuming.
That’s where batch processing and the calculatemultiple API method come in.
Why Multi-Record Processing Matters
When working with Excel-based rating models via SpreadsheetWeb, it’s common to feed in customer data from external sources—think of:
- A list of customer profiles for bulk quote generation
- Policy renewals needing re-rating
- Book-of-business repricing efforts
- Testing multiple scenarios for underwriting analysis
Rather than send one API call per row—which is possible but adds complexity and slows performance—SpreadsheetWeb offers a dedicated method to handle batch processing more efficiently.
Two Ways to Process Multiple Records via API
Looping Through Records in Power Query
In this approach, you loop through each row of your Excel table, calling the API once per record using Power Query’s List.Transform or Table.AddColumn functions.
While this method works, it comes with drawbacks:
- Performance impact: Each API call introduces latency and overhead.
- Complexity: You must handle individual error responses, retries, and rate limiting per row.
- Code maintenance: Managing a loop of web requests in Power Query can get messy.
Using calculatemultiple for Batch Requests
To simplify things, SpreadsheetWeb provides the calculatemultiple endpoint, which allows you to send multiple input records in a single API request.
This method has several advantages:
- Better performance – Fewer requests = faster results
- Cleaner implementation – One request, one response
- Easier error handling – Responses are grouped and indexed
How calculatemultiple Works
The calculatemultiple API method expects a list of input objects, each representing a set of input fields. Here's the basic structure:
{
"request": {
"requestId": "UNIQUE-ID-HERE", // Unique identifier for the request
"workspaceId": "your-workspace-id-here", // Replace with your actual Workspace ID
"applicationId": "your-application-id-here", // Replace with your actual Application ID
"localization": {
"decimalSeparator": ".", // Defines decimal separator (e.g., "." or ",")
"thousandsSeparator": ",", // Defines thousands separator (e.g., "," or ".")
"dateSeparator": "/", // Defines date separator (e.g., "/" or "-")
"currencySign": "$" // Defines currency sign (e.g., "$", "€", "₺")
},
"inputs": {
"Scenario1": {
"calculation": {
"inputs": [
{
"reference": "Input1", // The named range in your spreadsheet to populate
"value": [
[
{
"type": 0,
"formatType": 0,
"format": "General",
"text": "5000", // Displayed as text (optional)
"value": "5000", // Actual value used in calculation
"overwrite": 1 // 1 means it will overwrite existing data
}
]
],
"overwrite": 1
}
],
"outputs": [
"Output1" // Named range to retrieve the result from
],
"dynamicOutputs": [],
"validations": [
{
"reference": "Input1",
"retrieveValues": true,
"validate": true
}
],
"overwrite": 1
},
"goalSeek": {
"enabled": true,
"targetRef": "Output1", // The desired output cell reference
"changingRef": "Input1", // The input cell to be modified
"targetValue": 10000, // Desired output value
"maxIterations": 100, // Maximum number of iterations
"maxChange": 0.001, // Maximum change per iteration
"algorithm": 1 // Goal Seek algorithm (1 = basic, 2 = advanced)
},
"solver": {
"enabled": true,
"objective": "Output1", // Cell to maximize or minimize
"optimizationType": 1, // 1 = Maximize, 2 = Minimize
"valueOfGoal": 10000, // Target value for objective (optional)
"nonNegative": true, // True = result must be >= 0
"variables": [
"Input1"
],
"constraints": [
{
"operator": 1 // 1 = <=, 2 = >=, 3 = =
// Additional constraint parameters can be added
}
]
}
}
},
"recordInfos": {
"Scenario1": {
"id": 0,
"editId": 0,
"entryDate": "2025-04-11T11:13:35.297Z",
"lastUpdate": "2025-04-11T11:13:35.297Z",
"saveAsNewRecord": true,
"tags": {},
"loadExistingRecord": false,
"tagSavingBehavior": {
"assignTagsForSavingUser": false,
"assignTagsForAssociatedUserTags": false,
"assignSpecificTags": "",
"mergeTagsWhenUpdatingRecords": false,
"assignTagsByNamedRange": "",
"assignTagsByNamedRangeSequenceId": 0,
"retainExistingTags": false,
"tagsToAssign": []
}
}
},
"connectionString": "", // Optional external DB connection string
"friendlyTableTranslations": {},
"events": {
"print": [],
"excelExport": [],
"email": [],
"goalSeek": 0,
"solver": 0,
"queries": []
},
"transactionSequenceId": 0,
"transactionDate": "2025-04-11T11:13:35.297Z",
"executeEvents": true
}
}
The API returns a list of corresponding output objects, preserving the order of input records.
Implementing Batch API Calls in Power Query
Let’s walk through the basic steps to implement this in Power Query.
Prepare Your Data Table
Ensure your Excel table (e.g., InputData) contains all required fields as columns, with each row representing a distinct input scenario.
Get Bearer Token for /calculatemultiple API Call
To make an API call, a bearer token is needed. In this case, at the very beginning of code, a bearer token must be gotten.
TokenUrl = "https://identity.spreadsheetweb.com/connect/token", TokenBody = "grant_type=client_credentials&client_id=YOUR-CLIENTID&client_secret=YOUR-CLIENTSECRET", TokenResponse = Web.Contents(TokenUrl, [ Headers = [#"Content-Type" = "application/x-www-form-urlencoded"], Content = Text.ToBinary(TokenBody) ]), TokenJson = Json.Document(TokenResponse), AccessToken = TokenJson[access_token],
Transform Input Rows into a JSON Payload
Use Power Query to format your input rows into the data.inputs format expected by the API:
InputTable = Excel.CurrentWorkbook(){[Name="input_grid"]}[Content],
// clean necessary columns
CleanTable = Table.TransformColumns(InputTable, {
{"Base Policy Face Amount", each Text.Replace(Text.Replace(Text.From(_), "$", ""), ",", ""), type text},
{"Children's Insurance Rider Amount", each Text.Replace(Text.Replace(Text.From(_), "$", ""), ",", ""), type text},
{"Age", each Text.From(_), type text},
{"Premium Period", each Text.From(_), type text}
}),
// reference matches
ReferenceList = {
{"State", "State"},
{"PlanType", "Plan Type"},
{"PremiumPeriod", "Premium Period"},
{"IssueAge", "Age"},
{"Gender", "Gender"},
{"Class", "Tobacco Usage"},
{"PremiumMode", "Premium Mode"},
{"FaceAmount", "Base Policy Face Amount"},
{"ChildrenRiderAmount", "Children's Insurance Rider Amount"},
{"AccidentalDeath", "Accidental Death Benefit Rider"},
{"WaiverPremium", "Waiver of Premium"}
},
Make the API Request
Pass the JSON payload to the calculatemultiple endpoint using Web.Contents:
// API Request Body
RequestBody = Json.FromValue([
request = [
workspaceId = "YOUR-WORKSPACEID",
applicationId = "YOUR-APPLICATIONID",
transactionSequenceId = 2,
inputs = IndexedInputs
]
]),
RequestBodyText = Text.FromBinary(RequestBody),
// API call
ApiResponse = Web.Contents("https://api.spreadsheetweb.com/calculations/calculatemultiple", [
Headers = [
#"Content-Type" = "application/json",
Authorization = "Bearer " & AccessToken
],
Content = RequestBody
]),
ApiResponseText = Text.FromBinary(ApiResponse),
JsonResponse = Json.Document(ApiResponse),
Outputs = if Record.HasFields(JsonResponse, "response") and Record.HasFields(JsonResponse[response], "outputs")
then JsonResponse[response][outputs]
else error " There is no 'outputs' field in the API response. The request format may be incorrect.",
Parse and Expand the Results
The API response will be a list of output records. Use Power Query’s Record and List functions to convert this into a table you can work with in Excel:
PremiumList = List.Transform(
Record.ToList(Outputs),
each try
let
OutList = _[calculation][outputs],
PremiumObj = List.First(List.Select(OutList, each _[reference] = "TotalPremium")),
Val = PremiumObj[value]{0}{0}[value]
in
Val
otherwise null
),
// output table
ResultTable = Table.FromColumns({PremiumList}, {"TotalPremium"})
in
ResultTable
You can then expand the columns to expose the actual result fields.
Full Code for Batch Process Records SpreadsheetWeb API Power Query:
let
// 1) Get the access token
TokenUrl = "https://identity.spreadsheetweb.com/connect/token",
TokenBody = "grant_type=client_credentials&client_id=YOUR-CLIENT-ID&client_secret=YOUR-CLIENT-SECRET",
TokenResponse = Web.Contents(TokenUrl, [
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(TokenBody)
]),
TokenJson = Json.Document(TokenResponse),
AccessToken = TokenJson[access_token],
// 2) Read the input table from Excel
InputTable = Excel.CurrentWorkbook(){[Name="input_grid"]}[Content],
// 3) Clean and format necessary columns
CleanTable = Table.TransformColumns(InputTable, {
{"Base Policy Face Amount", each Text.Replace(Text.Replace(Text.From(_), "$", ""), ",", ""), type text},
{"Children's Insurance Rider Amount", each Text.Replace(Text.Replace(Text.From(_), "$", ""), ",", ""), type text},
{"Age", each Text.From(_), type text},
{"Premium Period", each Text.From(_), type text}
}),
// 4) Define mapping between API references and Excel column names
ReferenceList = {
{"State", "State"},
{"PlanType", "Plan Type"},
{"PremiumPeriod", "Premium Period"},
{"IssueAge", "Age"},
{"Gender", "Gender"},
{"Class", "Tobacco Usage"},
{"PremiumMode", "Premium Mode"},
{"FaceAmount", "Base Policy Face Amount"},
{"ChildrenRiderAmount", "Children's Insurance Rider Amount"},
{"AccidentalDeath", "Accidental Death Benefit Rider"},
{"WaiverPremium", "Waiver of Premium"}
},
// 5) Build input objects in dictionary format
IndexedInputs =
let
InputRows = Table.ToRows(CleanTable),
InputObjects = List.Transform(InputRows, each
let
RowRecord = try Record.FromList(
List.Transform(_, each if _ = null then "N/A" else _),
List.Transform(ReferenceList, each _{1})
) otherwise null,
InputsArray = if RowRecord <> null then
List.Transform(ReferenceList, (pair) =>
[
reference = pair{0},
value = {
{
[
type = 0,
formatType = 0,
format = "General",
text = try Text.From(Record.Field(RowRecord, pair{1})) otherwise "N/A",
value = try Text.From(Record.Field(RowRecord, pair{1})) otherwise "N/A"
]
}
}
]
)
else {},
CalcObj = if RowRecord <> null then
[
calculation = [
inputs = InputsArray,
outputs = {"TotalPremium"}
]
]
else null
in
CalcObj
),
CleanInputObjects = List.RemoveNulls(InputObjects),
InputKeys = List.Transform({0..List.Count(CleanInputObjects)-1}, each Text.From(_)),
FinalInputs = Record.FromList(CleanInputObjects, InputKeys)
in
FinalInputs,
// 6) Construct the API request body
RequestBody = Json.FromValue([
request = [
workspaceId = "YOUR-WORKSPACE-ID",
applicationId = "YOUR-APPLICATION-ID",
transactionSequenceId = 1,
inputs = IndexedInputs
]
]),
RequestBodyText = Text.FromBinary(RequestBody),
// 7) Make the API request
ApiResponse = Web.Contents("https://api.spreadsheetweb.com/calculations/calculatemultiple", [
Headers = [
#"Content-Type" = "application/json",
Authorization = "Bearer " & AccessToken
],
Content = RequestBody
]),
ApiResponseText = Text.FromBinary(ApiResponse),
JsonResponse = Json.Document(ApiResponse),
// 8) Extract outputs
Outputs = if Record.HasFields(JsonResponse, "response") and Record.HasFields(JsonResponse[response], "outputs")
then JsonResponse[response][outputs]
else error "The 'outputs' field is missing in the API response. Please verify the request format.",
// 9) Extract TotalPremium values from the response
PremiumList = List.Transform(
Record.ToList(Outputs),
each try
let
OutList = _[calculation][outputs],
PremiumObj = List.First(List.Select(OutList, each _[reference] = "TotalPremium")),
Val = PremiumObj[value]{0}{0}[value]
in
Val
otherwise null
),
// 10) Return as a table
ResultTable = Table.FromColumns({PremiumList}, {"TotalPremium"})
in
ResultTable
Conclusion
By using calculatemultiple and Power Query, you can scale your Excel-based insurance quoting process to handle hundreds of requests at once—all from within Excel.
This approach is faster, more maintainable, and ideal for:
- Batch quoting
- Renewal repricing
- Book-of-business analytics
- Underwriting automation
What’s Next?
If you're interested in seeing a working example or receiving a sample file, contact us and we’ll be happy to help.
Want to try it yourself? Head over to your SpreadsheetWeb workspace, generate your API key, and begin integrating your models in batch mode today!
