The Scenario
You're a solar energy analyst. Your team has 20 prospective site locations in a Google Sheet — columns A and B are latitude and longitude, 20 rows. The investment memo goes to the committee Thursday. The energy yield model needs 12 months of daily historical irradiance data for each site: GHI, DNI, and ambient temperature. That's 7,300 rows that need to land in a sheet called "Historical Data" before the model can even start.
The bad version:
- Open Solcast, enter site 1's coordinates, request the date range, copy the returned daily records, paste them into "Historical Data," label the rows with the site number
- Repeat for site 2, making sure you're appending and not overwriting site 1's data
- Do that 18 more times, then realize site 7's latitude was off by a decimal place and you need to re-pull that set
The committee isn't waiting while you re-pull site 7. The model needs clean inputs, and right now nothing is clean.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads the coordinate pairs, calls the Solcast API for each one, and writes the full time-series into the destination sheet — without you touching a single API credential or writing a line of code.
For each lat/lon pair in columns A and B (20 rows), fetch 365 days of historical daily irradiance data from Solcast ending yesterday, and write the site number, date, GHI, DNI, and ambient_temp into a sheet named 'Historical Data' — one row per day per site
What You Get
- A "Historical Data" sheet with 7,300 rows (365 days × 20 sites), labeled by site number in column A
- Date column formatted as YYYY-MM-DD, GHI, DNI, and ambient_temp in their own columns
- Sites processed in the same order as they appear in the input tab
- Any coordinate that returns an error from Solcast flagged in a "Status" column rather than silently skipped
What If the Data Is Not Quite Ready
The coordinates have extra decimal places and Solcast is rejecting them
For each lat/lon pair in columns A and B, round each coordinate to 4 decimal places before querying Solcast, then fetch 365 days of daily irradiance data ending yesterday and write site number, date, GHI, DNI, and ambient_temp into 'Historical Data'
Some rows are missing a longitude value
For each row in columns A and B where both latitude and longitude are present, fetch 365 days of historical irradiance from Solcast and write to 'Historical Data'. For rows where column B is blank, write 'missing longitude' into column C of the source tab instead of querying Solcast
The model also needs direct normal irradiance but the sheet has an older column named "Direct Normal" instead of DNI
Fetch 365 days of Solcast daily irradiance for each lat/lon pair in A:B, write GHI into the column labeled 'GHI' and DNI into the column labeled 'Direct Normal' in 'Historical Data', matching existing column names exactly
Clean up inconsistent coordinate formats, pull the irradiance, and flag outliers in one go
For each row in A:B, strip any degree symbols or spaces from the coordinates, then fetch 365 days of daily GHI, DNI, and ambient_temp from Solcast. Write results into 'Historical Data'. After writing, scan the GHI column and flag any daily value below 0.5 kWh/m² during June–August with 'low summer GHI' in an adjacent note column
The pattern is to fold the cleanup and the quality check into the same instruction — one prompt does the coordinate normalization, the data pull, and the anomaly flagging together.
Try It
Get the 7-day free trial of SheetXAI and open any Google Sheet with a list of site coordinates — even a rough draft with 5 rows — and ask it to pull 90 days of irradiance data for each one. Also see how SheetXAI handles exporting your full PV site inventory or the Solcast integration overview.