AMI Deployment Map in Google Sheets: A Pre-Sales Workflow

Build an AMI deployment map in Google Sheets by geocoding service points with =GEOCODE(A2:A5000), calculating signal distance to nearest gateways using =DISTANCE(), and flagging coverage with =WITHIN_RADIUS(). Finally, =INSTAMAP() generates a live, shareable map URL without batch limits or manual exports.

This workflow is designed for pre-sales engineers and utility project managers who are tired of batch-capped geocoders that process 500 records at a time, taking 5 to 10 minutes per batch. The end state is a complete, customer-facing fixed-network proposal that updates automatically as you adjust service points or gateway locations.

The Pre-Sales Geocoding Bottleneck

Pre-sales and deployment engineers at metering vendors routinely hit the same wall during AMI fixed-network proposals. You receive a raw utility customer list of 15,000 service-point addresses. To model the network, you need coordinates. The standard workflow involves exporting the list to a CSV, uploading it to a web-based geocoding tool, and waiting. These tools typically cap uploads at 500 records per batch. For 15,000 addresses, that is 30 separate uploads.

Each batch takes 5 to 10 minutes to process, flush, and download. You spend hours manually babysitting progress bars instead of designing the network. This batch-capped copy-paste-wait workflow breaks down entirely when utility PMs request last-minute revisions. If 200 new addresses are added to the scope, you must re-export, re-batch, and re-import the data.

InstaMaps bypasses this bottleneck by moving the geocoding process directly into the Google Sheets cell where the data lives. Instead of navigating external web portals, deployment engineers apply spreadsheet formulas that process ranges locally and iteratively. The Extensions > InstaMaps > Enable formulas sidebar writes the chains directly into the sheet.

You can process tens of thousands of rows without leaving your spreadsheet, and when a utility PM adds a block of meters to row 4,502, the coordinates calculate on the next sheet edit without requiring a manual batch re-upload. When a web geocoder fails, it spits out an unlabelled error code in a separate text file. Locating the 12 failed rows in a 500-row export requires manual filtering. Spreadsheet formulas return errors directly to the cell, such as #N/A for an unparseable address or a quota warning, letting you use standard Google Sheets filters to isolate and fix exact problem rows instantly.

Step 1: Geocoding the Service-Point List

Start with the raw AMI service-point list provided by the utility. Assume column A contains the street address, column B contains the city, and column C contains the postcode. To prepare the data for mapping and RF modelling, you need latitude and longitude coordinates.

Instead of exporting the data, use =GEOCODE(). If you concatenate the full address into column E (=A2&", "&B2&", "&C2), you simply drop the following formula into cell F2: =GEOCODE(E2:E5000). This function processes the addresses directly within the sheet. It outputs a clean "lat, lng" string for the entire range. Because the formula lives in the cell, it recalculates automatically when data changes.

If you are working with a massive deployment list of 20,000 meters, you do not need to babysit a progress bar. However, you must be mindful of daily quota limits to prevent the formula from timing out. The free tier permits 100 lookups per day. For deployment engineers building an AMI proposal, this limit is restrictive, but registering for a free email unlock increases the daily limit to 1,000 lookups per day.

At 1,000 lookups per day, geocoding a 5,000-meter pilot programme takes five days of background processing, or less if historical address data is reused. If the utility PM drops 47 new street addresses into row 3,001, you just copy the formula down to row 3,047. The add-on returns coordinates like 40.7128, -74.0060 alongside the existing data, eliminating the need to match and merge external CSV files.

Step 2: Distance to Gateway and Coverage Flags

Once the 5,000 service points have coordinates, the next phase of AMI fixed-network design is determining RF coverage. You need to measure the distance from each meter to the proposed gateway or access point.

Place your proposed gateway coordinates in cell H2 (for example, 40.7128, -74.0060). In column G, calculate the straight-line distance from each meter to the gateway using the =DISTANCE() function: =DISTANCE(F2, $H$2). This outputs the distance in kilometres. If your RF engineering rules dictate a maximum range of 1.5 kilometres for this specific endpoint hardware, you need to flag meters outside this threshold.

Instead of writing complex IF() statements, use InstaMaps' =WITHIN_RADIUS() function. In cell I2, input: =WITHIN_RADIUS(F2, $H$2, 1.5). Drag this down to row 5,000. The function returns TRUE if the meter falls within the 1.5km radius of the gateway, and FALSE if it falls outside the coverage area.

This boolean output is critical for deployment engineers calculating network density. You can quickly apply a standard Google Sheets filter to column I to count the FALSE values. If 400 out of 5,000 meters return FALSE, you immediately know you need to propose a secondary gateway to cover the gaps in the utility’s territory.

If a specific address returns an error like #VALUE!, it indicates malformed coordinates in column F-usually caused by a missing postcode in the original utility export. The =CLEAN_ADDRESS() function can standardise the raw utility text before it goes into =GEOCODE(), ensuring the =DISTANCE() calculations do not break halfway down the sheet.

Using exact row counts and hard radius cut-offs ensures your bill of materials is accurate. Furthermore, if you need to evaluate a backup gateway location, simply change the coordinate string in cell H2. Every =DISTANCE() and =WITHIN_RADIUS() cell updates instantly, providing immediate feedback on hardware repositioning without manual recalculation.

Step 3: Generating the Customer-Facing AMI Deployment Map

The final step in the Alexander workflow is presenting the AMI fixed-network proposal to the utility project managers. Utility PMs do not want to install Google Earth, manipulate shapefiles, or request Google account access to view a private My Maps creation.

InstaMaps handles this with the =INSTAMAP() function. Instead of exporting your sheet to a third-party mapping software, you generate a hosted map URL directly from the cell. Select your meter coordinates in column F, add a standard filter to only show the TRUE flags from column I (your covered meters), and enter: =INSTAMAP(F2:F5000).

The function returns a live, hosted map URL in the cell. Clicking it opens a browser-based map displaying all 5,000 service points. This link is entirely shareable; utility PMs can open it on their mobile phones or laptops without logging into a Google account. More importantly, because the map is tied to the cell range, it updates automatically when you adjust the sheet data. When you move the gateway in cell H2, the =WITHIN_RADIUS() flags change, and the =INSTAMAP() link immediately reflects the new coverage boundaries the next time the PM refreshes the URL.

You can explore a live version of this setup using the gateway-showcase demo sheet (https://get-instamaps.com/templates/gateway-showcase-demo). This workflow allows pre-sales engineers to build proposals where the underlying data and the visual output exist in the same file. By splitting the data into two tabs-Sheet1 for covered meters (TRUE) and Sheet2 for out-of-range meters (FALSE)-you can generate two distinct =INSTAMAP() links.

This gives the utility a clear visual of network health without a single manual export. For further configurations, the sidebar’s Build-the-workflow button provides ready-made templates at get-instamaps.com/templates to map out complex fixed-network designs.

Worked Example: 200-Home Fixed-Network Proposal

Consider a 200-home neighbourhood pilot in Bristol. Pre-sales engineers typically paste these service-point addresses into batch-capped web geocoders, waiting 10 minutes per 500 records. In Google Sheets, paste the raw meter addresses into range A2:A201. Select cell B2 and enter `=GEOCODE(A2:A201)`. The add-on processes the array, populating B2:C201 with latitude and longitude pairs.

Place the planned collector gateway coordinates in cell F2. To determine the radio link budget, calculate the physical distance to the endpoint. In D2, input `=DISTANCE(B2:C201, F2)`. This returns the distance in metres for all 200 rows.

Assume the RF module signal degrades past 300 metres. In E2, use `=WITHIN_RADIUS(B2:C201, F2, 300)`. This returns a boolean TRUE/FALSE flag for each row. Filtering column E reveals exactly 47 addresses flagged as FALSE, indicating they require repeaters.

To visualise this, select G2 and enter `=INSTAMAP(A2:E201)`. This generates a live hosted map URL. You email this link directly to the utility PM. Because the map reads directly from the spreadsheet, if you adjust the gateway coordinates in F2, the hosted map updates instantly. You can avoid typing entirely by opening the sidebar (Extensions > InstaMaps > Enable formulas) and clicking the Build-the-workflow button to write these chains automatically. View the exact layout in our gateway-showcase demo sheet (https://get-instamaps.com/templates/gateway-showcase).

Limits and Honest Alternatives

InstaMaps operates on a free tier with strict usage quotas. The base tier provides 100 lookups per day. Registering with an email address increases this capacity to 1,000 lookups per day. An engineer processing a 200-home neighbourhood hits this quota in a single afternoon. When the daily limit is exhausted, the function returns an explicit error in the cell, halting the chain calculation until the 24-hour counter resets.

For standard AMI pilots, 1,000 daily lookups are sufficient. However, this workflow breaks down when pre-staging a municipal rollout of 50,000 meters. Processing 50,000 rows would take 50 days at 1,000 rows per day. Additionally, the Google Sheets calculation engine will slow down or time out when evaluating `=DISTANCE()` or `=WITHIN_RADIUS()` arrays across tens of thousands of rows simultaneously.

InstaMaps calculates straight-line distance. It does not account for elevation data, terrain shadowing, or foliage attenuation. If you are planning a mesh network in a topographically complex region, straight-line radius checks are insufficient for accurate RF modelling.

If your deployment requires processing over 10,000 service points, incorporating digital elevation models, or running complex polygon containment, use desktop GIS software. QGIS is the ideal alternative. It handles coordinate reference systems natively without browser memory limits. Use QGIS to build RF heatmaps, run the GRASS GIS r.viewshed module for terrain analysis, and export shapefiles. InstaMaps is a pre-sales and tracking tool, not an enterprise RF engineering suite.

Who This Workflow Is For

This workflow is designed for two specific roles in the utility sector:

1. Pre-sales engineers at metering vendors: You respond to RFPs and build fixed-network proposals. When a utility asks 'What happens if we move the gateway 200 metres east?' you can adjust the coordinate in cell F2 and immediately present the updated coverage flags. You bypass the 500-record batch caps and 10-minute processing delays of traditional web tools.

2. Utility programme managers (PMs): You require transparent deployment tracking without purchasing enterprise GIS software licences. You can monitor service-point statuses, identify the 47 meters requiring repeaters, and share a hosted map URL with stakeholders.

3. If your deployment scope involves neighbourhood pilots, circuit-level upgrades, or subdivisions under 1,000 meters, this removes the friction between spreadsheet tracking and map generation.

Common Questions