Nightly DynamoDB table export to Google Sheets

Every night at 1am on a cron schedule, copy every item from a chosen Amazon DynamoDB table into a designated tab of a Google Sheet so non-technical teammates can slice the data without touching AWS. The workflow is fully deterministic: scan the whole table, flatten each item, clear the target tab, then write a header row and all rows back in one pass.

Setup inputs the workflow should collect: the DynamoDB table name and region, the Google Sheets spreadsheet ID and tab name (default the tab to "raw"), and an optional ProjectionExpression (with matching ExpressionAttributeNames if any of the requested attribute names are reserved words) so the operator can limit which columns get exported.

Step 1 — Scan the DynamoDB table. Use the Amazon DynamoDB Scan operation and page through the entire table by following the pagination cursor: on the first request omit ExclusiveStartKey; on each subsequent request pass the LastEvaluatedKey from the previous response as ExclusiveStartKey; stop when the response no longer includes LastEvaluatedKey. Accumulate every returned item into a single in-memory list. If the operator supplied a ProjectionExpression at setup, forward it (plus ExpressionAttributeNames when needed) on every Scan page so DynamoDB only returns the requested attributes. Handle ProvisionedThroughputExceededException and ThrottlingException with exponential backoff before retrying the same page.

Step 2 — Flatten DynamoDB's tagged attribute-value format into plain spreadsheet values. Every attribute in a Scan response is wrapped in a single-key type tag: {"S": "text"} becomes the string, {"N": "42"} becomes the number 42 (DynamoDB always sends numbers as strings; parse them), {"BOOL": true} becomes true, {"NULL": true} becomes an empty cell, {"SS"|"NS"|"BS": [...]} becomes a comma-joined string, {"L": [...]} and {"M": {...}} are recursively unwrapped and then JSON-stringified so the cell shows something readable rather than raw DynamoDB syntax, and {"B": "..."} binary values become a short placeholder like "<binary>". Do this recursively so nested maps and lists inside items are unwrapped all the way down before being stringified.

Step 3 — Derive the column order. Union the attribute names across every scanned item (DynamoDB items are schemaless, so different rows may have different attributes). If a ProjectionExpression was supplied, use exactly that list in the order the operator provided. Otherwise sort alphabetically but pin the table's partition key (and sort key, if any) to the front. This ordered list is both the header row and the column order used when building each data row.

Step 4 — Clear the target tab. Call Google Sheets Clear Values against the whole tab (A1 notation like "raw" or "raw!A:ZZ") before writing anything, so the sheet reflects the current state of the DynamoDB table rather than growing forever across runs. Clear Values preserves formatting and data validation, which is what we want.

Step 5 — Write the snapshot. Use Google Sheets Append Values with valueInputOption=RAW against the target tab. First append a single row containing the derived header names. Then append the flattened data rows, chunked into batches (a few thousand rows per Append Values call is safe) so no single request exceeds the ~2 MB payload guidance. Missing attributes in a given item should be written as empty cells so every row has the same width as the header.

Nuances to bake in: the workflow always writes a fresh header row each run (because clearing wipes the previous one), so there is no separate "first run of the day" branch — treat every run as a full refresh. Log the number of items exported and the elapsed time so the operator can spot silent drift. Fail loudly if the Scan returns zero items but the table's DescribeTable item count is non-zero, since that usually means the ProjectionExpression is malformed.