# Interlanguage from Google Sheets 1. Create a private Google Sheet. Open Extensions → Apps Script. 2. Paste `Code.gs`, save, and reload the spreadsheet. 3. Choose Interlanguage → Set API key. Enter the key created on Account and usage. Authorize the script when Google prompts. 4. Choose Test connection. This uses `https://api.interlanguage.ai`, not the browser `/api` route; no Cloudflare login cookie or service token belongs in this script. 5. Paste one JSON object below in a cell, select it, and choose Initiate text analysis or Initiate audio analysis. The cell immediately to its right must be empty. It shows Running… and then JSON containing actual CSV report tables. Text (explicit English avoids an ambiguous language review): ```json {"student_id":"sheetstest-01","text":"The students carefully studied the difficult passage before they discussed its meaning together.","sample_language":"en-US","analyzers":["L2SCA"]} ``` Text plus audio (replace Drive ID with a file you can access): ```json {"student_id":"sheetstest-02","text":"The students carefully studied the difficult passage before they discussed its meaning together.","audio_drive_id":"YOUR_DRIVE_FILE_ID","sample_language":"en-US","analyzers":["AZURE"]} ``` Audio only (ASR/transcription and pronunciation can incur existing analysis charges): ```json {"student_id":"sheetstest-03","audio_drive_id":"YOUR_DRIVE_FILE_ID","sample_language":"en-US","analyzers":["AZURE"]} ``` Use a Drive file ID, not a publicly shared audio URL. Audio is uploaded as base64 from Drive; this starter client limits it to 10 MB. The API allows larger uploads using its chunked-upload endpoints. IDs must be JSON strings to preserve leading zeros. Analysis names: L2SCA, TAALED3, TAALED3.1, TAASSC, TAACO, TAALES, GRAMMAR, AZURE, ACOUSTIC. AZURE is Azure pronunciation assessment; ACOUSTIC is the separate acoustic-measures analysis. The Sheets client accepts these names case-insensitively and maps them to existing API IDs (syntax, taaled3, taaled3.1, taassc, taaco, taales, grammar, speech, acoustic). Older JSON remains compatible. Report labels and the exported Analysis column use the readable names. Check supported language/analyzer combinations before selecting them; English-only analyzers are not automatically switched for another language. Grammar can use `target_language` for translated reports. ## Retrieval - Job ID in a cell → Query by job writes its report to the adjacent empty cell. Unfinished jobs return their status. - Blank sheet → Query all results gives long-form metrics (one metric/value per row). - A populated sheet prompts before replacing contents. API failures do not clear the sheet. - Export covers retained account results, not deleted historical jobs. The API export has a 100,000-row bound. This initial client does not paginate beyond that bound. - Export currently includes the API's private learner ID column. This is an opaque account-scoped identifier, not a decrypted student number. - CSV tables are returned as arrays including header rows. This client does not embed PDFs or download non-CSV artifacts. Large JSON reports above 49,000 characters require sheet export. ## Execution and safety There are no scheduled polling triggers. After submission, a foreground timer updates the output cell approximately every second and checks job status every 15 seconds for up to three minutes. It stops on completion, failure, cancellation, or three consecutive status-request errors. Query by job resumes this bounded waiting period for an unfinished job. Each successful status request currently costs USD 0.0002; timer updates themselves are free. Initiate text/audio analysis performs synchronous intake review and then submits the reviewed job in the same menu action. If review fails, it reports the issue and does not start analysis. After submission, the output cell shows Running… and the job ID. Select that cell and use Query by job whenever you want its current status or completed results. Queries never submit or restart work. Old polling triggers remove themselves without API requests; initiating or querying also removes old triggers. Submission POSTs are never automatically retried after uncertainty. Query the recorded job ID and check the website before resubmitting. Failed analyses are displayed, not automatically restarted. If the initial synchronous review exceeds Google's execution time limit, no automatic analysis submission occurs. Keep the spreadsheet private or share only with trusted editors: editors can change bound scripts. The API key is in your user properties, never a cell or log. Reports and sample inputs remain in the spreadsheet, subject to its sharing permissions. Exported formula-like values are escaped. ## Billing Existing analysis charges remain separate. After deploying/restarting the updated backend, every successful (2xx) personal-key request accrues USD 0.0002 (USD 2 / 10,000). This includes connection tests, reviews, submissions, manual status queries, exports and each artifact download. Failed requests, browser bridge requests and internal service-key traffic have no API fee. One analysis involves multiple API requests. Foreground polling runs only during a bounded active wait, not in the background. Fees are recorded in `api-call-usage.sqlite` as integer USD micros, keyed by server-generated request ID, account, time, method, route template and HTTP status. They are deferred/unbilled, with no automatic collection or rounding per request. `/account/usage` includes `api_calls` with current-month, lifetime and unbilled totals (its own successful call is recorded after the response). API fees currently sit outside the existing analysis spending cap and wallet; they are not yet invoiced or deducted from credit. Historical calls are not backcharged. Preserve this database during backup/restore. ## Validation JavaScript syntax and mocked API workflows are tested locally. Google Apps Script authorization, actual API-key access through Cloudflare from Google's servers, and live paid processing have not been tested in a Google account. Use Test connection then a single syntax sample first. An HTTP 403/redirect is an access configuration problem, not a reason to place owner/service credentials in the script. Google references: https://developers.google.com/apps-script/guides/menus and https://developers.google.com/apps-script/guides/triggers/installable ## Build JSON from cells Select input cells only, without a header row. Multiple rows are supported. - **Build text JSON:** Analysis type | Student ID | Text → JSON in the fourth column. - **Build audio JSON:** Analysis type | Student ID | Audio URL → JSON in the fourth column; or select a fourth Transcript column (which may be blank) → JSON in the fifth column. Use comma-separated analysis names for multiple analyses, for example `L2SCA, GRAMMAR`. IDs must be one string without whitespace. Format ID cells as plain text before entering leading zeros; the builder preserves their displayed value. Text and nonempty optional transcripts require at least five space-separated words. Audio links must begin with HTTP or HTTPS and point to downloadable media, not an HTML sharing page. The builder validates URL form only; the server checks download access and media content when analysis is initiated. URL samples use the API's CSV audio intake. Existing Drive-ID JSON remains supported. Each output cell must be blank. Invalid rows and occupied outputs are listed in a message; valid rows still get their JSON. No input or occupied output is replaced. Builders make no API requests. Select an individual generated JSON cell to initiate its analysis. **Expand report JSON:** select the completed JSON report cell. Labeled analysis tables expand vertically to its right with metric names on the left and values on the right. The entire destination rectangle must be blank. The original JSON remains intact. This operation also makes no API request. ## PDF links Completed job queries also download available PDFs into your Google Drive (Google may request Drive permission). The report JSON contains a `pdfs` array with Drive links; Expand report JSON adds labeled PDF-link rows beneath the tables. Files are created in your Drive root without enabling public/link sharing. Repeated queries reuse the saved copy unless deleted or inaccessible. Each first PDF download is another API request. Drive errors are reported separately and do not discard CSV results. Query all results remains a metric export; PDF copies are created by querying an individual completed job. ## Direct actions (top of menu) 1. Initiate text analysis: select Analysis type | Student ID | Text, one or multiple rows. 2. Initiate speech analysis: select Analysis type | Student ID | Audio URL, optionally including a fourth transcript column. 3. View results by job: select a job-ID/status/result-JSON cell. Completed results appear as tables immediately to its right; the full destination must be blank. Unfinished jobs show their current status in a dialog. 4. View all results: exports the account's retained metrics into the current sheet, confirming before replacing populated contents. Direct submission skips creating an input JSON cell. Outputs go immediately after the selected columns. Single-row submission retains the foreground timer and compact result JSON; use View results by job for tables. Multi-row submission queues each row and leaves its job ID/status without waiting on each job, avoiding a long blocking timer per row. No scheduled polling is started. All rows are validated and output cells checked before the first submission. A time limit stops before starting further rows; it does not resubmit prior rows. The lower menu retains JSON builders, submission from JSON, Query by job — JSON, Query all results — JSON, and Expand report JSON. Query all results — JSON requires one selected blank cell and is limited by Sheets' single-cell size; use View all results for large exports. ## Invite users and errors Choose ✉️ Invite users ✉️ and enter one email address. The existing POST /invites endpoint now accepts an enabled personal API-key account with invitation permission. Invitations are attributed to that account and sent by Interlanguage's configured email service, not Gmail or a spoofed sender address. Existing account isolation, accepted-account requirement, 50-new-invitations-per-day limit and duplicate protection remain in effect. Backend restart is required for this permission update. No email is sent merely by installing the script. Queued does not mean delivered; uncertain requests are not automatically retried. All menu actions show a labeled error dialog. HTTP errors include suggested action for authentication, permissions, invalid input, spending limits, queue limits and service failures. Existing per-row error messages remain available for batch builders/submissions. Google-managed authorization prompts and hard execution timeouts are controlled by Google and cannot always be intercepted by the script.