标签：#googlesheets

Every logistics and field-sales team runs the same expensive process: a driver photographs a receipt into a WhatsApp group, and a back-office clerk manually types the invoice number, total, and date into a spreadsheet. Hundreds of receipts a week = transcription errors and thousands of wasted hours. AI vision models kill that bottleneck. Here's the pipeline that turns a blurry field photo into clean structured data in seconds. Why vision models beat traditional OCR OCR reads characters. Modern vision models (Claude Vision, Gemini Vision, GPT-4 Vision) read structure — they distinguish a tax ID from a total, and a date from an amount, even on crumpled, angled, or poorly lit receipts. No brittle per-vendor parsers. The pipeline (3–8 seconds end to end) WhatsApp image → Apps Script doPost → forward to vision model → model returns JSON { InvoiceNumber, TotalAmount, VendorName, Date, Category, confidence_score } → confidence routing: > 90 → auto-append to ledger 70–90 → flag for human review < 70 → ask driver to re-photo → write row to Google Sheet (+ link to original image) → auto WhatsApp confirmation to driver The confidence_score is the whole trick — it's what stops bad extractions from silently polluting your ledger. Model selection (this drives your bill) Gemini Vision — cost-efficient default, strong multilingual OCR, great on clean receipts. Claude Vision — highest accuracy on degraded receipts; use for high-stakes flows. GPT-4o Vision — competitive, strong structured extraction. Pattern: Gemini for the first pass, escalate only low-confidence cases to Claude / GPT-4o. The economics ~500 receipts/week: vision API $10–40 + WhatsApp API $30–60 + Apps Script free = ~$40–100/month . Versus a clerk at ~25 hrs/week = $2,000–4,000/month in loaded labor. Per-receipt cost: $0.005–0.02 (compress images to ~1024px to cut it further). Accuracy: 92–97% on legible receipts, 75–85% on handwritten/damaged — hence the confidence routing. Pitfalls to avoid Auto-appending with no c

Field-service software has an adoption problem: drivers won't use it. Heavy app, another login, crashes in low-signal areas. So the "real-time" data still shows up as end-of-shift phone calls. The fix that actually sticks: stop building an app and use the one drivers already live in — WhatsApp. With Apps Script and Google Sheets behind it, WhatsApp becomes a frictionless mobile ERP. Here's the build. WhatsApp as a data-entry terminal A driver texts Status ABC-1234 Delivered . An Apps Script doPost webhook receives it, parses it, and updates the Sheet in real time. Latency goes from hours to milliseconds — and there's nothing to install, so adoption hits 90%+ in a week (vs. 50–70% for custom apps). Two-stage parsing for messy input Real drivers type "done," not clean commands. So: Regex first pass — handles ~70% of messages (clean format) instantly and for free. LLM fallback — the remaining ~30% goes to a cheap model (GPT-4o-mini / Gemini Flash) with the known cargo IDs and valid statuses. It returns normalized JSON + a confidence score. Below-threshold messages surface to a dispatcher. The LLM normalizes correctly 95%+ of the time (~5% manual), and it handles multilingual input with zero extra code. Driver msg → Apps Script doPost → regex pass → (fail) LLM fallback w/ confidence score → Sheet update (timestamp + raw-message log) → optional outbound (route change, POD photo request) Why Google Sheets is the right backend Dependent formulas: time-to-delivery, SLA-breach flags Pivot tables for reporting Apps Script triggers for automatic client emails Conditional formatting dashboards Native Calendar / Maps / Drive integration (POD photos → Drive folder) It runs on free Google Workspace infrastructure with minimal API cost. Bidirectional by default The same integration pushes messages back to drivers: route changes, delivery instructions, shift reminders, exception alerts, proof-of-delivery photo requests — all in the same thread. Pitfalls that get your number banned T

If you send SMS one-time codes, there's a decent chance you're paying scammers to phone-spam themselves on your dime. It even has a name: SMS pumping . And it's not a rounding error — Elon Musk claimed Twitter was losing ~$60M/year to fake 2FA traffic before they killed SMS 2FA for free accounts. Here's how the scam works, why SMS 2FA is structurally expensive, and why flipping the direction — mobile-originated (MO) 2FA , taken to its logical end over iMessage — fixes both the cost and the fraud at once. What is SMS pumping? SMS pumping (also called AIT — Artificially Inflated Traffic , or SMS toll fraud ) is a scheme where bad actors abuse a form that sends SMS one-time codes. They pump thousands of phone numbers — usually premium ranges they secretly control with a telecom — into your "send me a code" endpoint. You pay for every one of those messages. A cut of that termination fee flows back to the fraudsters via the carrier. The "users" never log in. They were never users. The entire point was to make your verification endpoint dial a meter that pays them. The structure that makes this possible is simple: you, the company, send (and pay for) the message. Every code is revenue for someone in the delivery chain — so there's a direct financial incentive to trigger as many as possible. Why SMS 2FA is expensive even without fraud Even with zero abuse, application-to-person ( A2P SMS ) is a bad cost curve: You pay per message. Volume spikes — a launch, a bot attack, an international audience — turn into surprise bills. International is brutal. Cross-border A2P carries steep carrier surcharges that vary wildly by destination. Carrier fees and registration overhead. In the US you're funneled through A2P 10DLC registration, brand vetting, and per-segment fees before you send a single legit code. So your 2FA line item is pay-per-event , unpredictable , and exploitable . Three bad properties for something that's supposed to be boring infrastructure. The Twitter/X case This

Originally written for bulldo.gs — republished here with the canonical link pointing home. I want to pull live JSON data from an API endpoint directly into a Google Sheet without installing an add-on. // Fetch JSON from an API and write it to the active sheet // Adjust API_URL and the field list to match your endpoint function importJsonFromApi () { var API_URL = ' https://jsonplaceholder.typicode.com/users ' ; var sheet = SpreadsheetApp . getActiveSpreadsheet (). getActiveSheet (); var response = UrlFetchApp . fetch ( API_URL ); var raw = response . getContentText (); var data = JSON . parse ( raw ); var headers = [ ' id ' , ' name ' , ' username ' , ' email ' , ' phone ' ]; var rows = data . map ( function ( obj ) { return headers . map ( function ( key ) { return obj [ key ] || '' ; }); }); sheet . clearContents (); sheet . getRange ( 1 , 1 , 1 , headers . length ). setValues ([ headers ]); sheet . getRange ( 2 , 1 , rows . length , headers . length ). setValues ( rows ); } Why getContentText() comes before JSON.parse UrlFetchApp.fetch() returns an HTTPResponse object, not a string. The first time I skipped getContentText() and passed the response object directly to JSON.parse(), it silently parsed to null and the sheet wrote nothing. You need raw = response.getContentText() to get the actual body as a string, then JSON.parse(raw) turns it into a JavaScript object or array. The URL must be publicly accessible or accept an API key via a query parameter or Authorization header. Add headers like this: UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + token } }). Apps Script's UrlFetchApp quota is 20,000 calls per day on a free Google account, 100,000 on Workspace. The rectangular array constraint — why setValues fails without mapping setValues() is strict: it requires a 2D array where every row has the same number of columns. If you hand it an array of plain JSON objects, it throws 'The number of rows or columns in the range does not match the number of

Originally written for bulldo.gs — republished here with the canonical link pointing home. I have a Google Sheet with duplicate rows and I want to remove them programmatically, either on demand or on a schedule, without destroying the rest of my data. // removeDuplicates.gs — dedup active sheet, keep first occurrence // Run from Extensions > Apps Script, or bind to a trigger. function removeDuplicateRows () { var sheet = SpreadsheetApp . getActiveSheet (); var data = sheet . getDataRange (). getValues (); var seen = new Set (); var unique = []; for ( var i = 0 ; i < data . length ; i ++ ) { var key = data [ i ]. join ( ' | ' ); if ( ! seen . has ( key )) { seen . add ( key ); unique . push ( data [ i ]); } } sheet . clearContents (); sheet . getRange ( 1 , 1 , unique . length , unique [ 0 ]. length ). setValues ( unique ); } Why rewrite instead of delete The instinct when deduplicating is to loop through the sheet and call deleteRow on each duplicate. That works, but it has a sharp edge: every call to deleteRow shifts all rows below it up by one. If you delete row 3, what was row 4 is now row 3, and your loop index is already pointing at the new row 4. The safe workaround people reach for is iterating bottom-to-top, which works but means holding the full duplicate set in memory anyway, making one API call per deleted row. The approach here sidesteps the problem entirely. Read everything once with getDataRange().getValues() — a single API call that returns a 2D array. Build the deduplicated array in JavaScript using a Set to track which row fingerprints you have already seen. Then clear the sheet and write the result back with one setValues call. Two API calls total, regardless of how many duplicates you had. For a 10,000-row sheet, this is the difference between a script that finishes in two seconds and one that times out at the six-minute Apps Script execution limit. The row key is built with data[i].join('|'). The pipe character works as a separator in practice; i

I got tired of paying for form services or spinning up a backend just to handle contact form submissions. So I built RG Forms — a contact form endpoint backed entirely by a Google Sheet you own. No server, no monthly fee, no third-party storing your data. The idea Most form tools store your submissions on their servers. You pay monthly, you depend on their uptime, and your data lives in their database. RG Forms does the opposite: every submission goes straight into a Google Sheet in your own Google Drive, sent by an Apps Script that you own and control. RG Forms provisions that sheet and script for you in about 90 seconds. After that, your endpoint runs forever at no cost — completely independent of any RG Forms server. Built for static sites If you host on GitHub Pages, Netlify, Vercel, Cloudflare Pages, or just plain HTML on a CDN, you've hit this wall: there's no backend to receive a form POST. The usual workarounds are a paid form service, a serverless function you have to write and maintain, or standing up a whole backend just for a contact form. RG Forms is built exactly for this. Your endpoint is a plain HTTPS URL you POST to straight from client-side JavaScript — no build step, no serverless function, no server of any kind. Drop the fetch call into your page and you're done. It pairs naturally with any static-site generator (Hugo, Jekyll, Astro, Eleventy, Next export) and any no-code builder that lets you add a snippet of JS. Your static site stays static; the form just works. How it's built RG Forms is a fully static web app. There's no RG Forms server, no database, no backend. Every API call during setup goes directly from your browser to Google using your own OAuth token. Setup (one time, in your browser): Your Browser ├─── Google OAuth ──▶ Short-lived token (memory only) ├─── Google Drive API ──▶ Creates Sheet + Drive folder └─── Apps Script API ──▶ Creates & deploys form handler Live endpoint (after provisioning): Your Website / App └─── POST to script