Cloud Storage is a common landing zone for warehouse loads. Apps Script can POST a CSV object with uploadType=media.
This sample builds CSV from ExportReady display values, names the object under exports/ with a timestamp, and authorizes with the script OAuth token.
You must declare the cloud-platform or devstorage scope in appsscript.json and grant the user access to the bucket.
Prefer service accounts for unattended production; this tutorial keeps the user-token path for simpler demos.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Example | Notes |
|---|---|---|
| Bucket | your-bucket-name | BUCKET constant |
| Object prefix | exports/ | Folder-like prefix |
| Source sheet | ExportReady | CSV source |
| Scope | https://www.googleapis.com/auth/devstorage.read_write | appsscript.json oauthScopes |
| API | storage.googleapis.com upload | media upload |
What this script does
uploadSheetCsvToGcs() serializes ExportReady to CSV and POSTs it as a new object in your bucket.
csvEscape_ handles commas, quotes, and newlines so warehouse parsers stay happy.
Prerequisites
A GCS bucket, OAuth scope configured, and IAM permission for the running user to create objects.
- appsscript.json includes storage scope
- ExportReady sheet populated
- BUCKET name exact
Walkthrough
Read display values, join CSV lines, build the upload URL with bucket + object name, fetch with Bearer token, parse metadata JSON.
Edge cases
Display values stringify dates using spreadsheet locale — switch to getValues() + explicit formatting if you need ISO.
- 403 usually means missing IAM or scope
- Object names are immutable overwrite-by-name unless you version
How to test
Run once, then gsutil ls gs://your-bucket-name/exports/ or check the console.
Hardening for production
Use a service-account JWT for triggers; set muteHttpExceptions and surface resp text; add Content-MD5 if required.
Variations
Upload JSON lines, or zip via Utilities.zip and contentType application/zip.
Full code: uploadSheetCsvToGcs()
Add the storage OAuth scope, set BUCKET, then run uploadSheetCsvToGcs() from the editor.
/**
* Upload a sheet export CSV to Cloud Storage using a signed OAuth token
* from ScriptApp (GCP scope) or a service-account token stored in Properties.
* Here we use UrlFetchApp with a bearer token from ScriptApp.getOAuthToken()
* and the Cloud Storage JSON API — enable scope in appsscript.json.
*/
const BUCKET = "your-bucket-name";
const OBJECT_PREFIX = "exports/";
const SOURCE_SHEET = "ExportReady";
function uploadSheetCsvToGcs() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(SOURCE_SHEET);
if (!sheet) throw new Error("Missing " + SOURCE_SHEET);
const values = sheet.getDataRange().getDisplayValues();
const csv = values.map(function (row) {
return row.map(csvEscape_).join(",");
}).join("\n");
const objectName = OBJECT_PREFIX + Utilities.formatDate(
new Date(), Session.getScriptTimeZone(), "yyyyMMdd_HHmmss") + ".csv";
const url = "https://storage.googleapis.com/upload/storage/v1/b/" +
encodeURIComponent(BUCKET) + "/o?uploadType=media&name=" +
encodeURIComponent(objectName);
const token = ScriptApp.getOAuthToken();
const resp = UrlFetchApp.fetch(url, {
method: "post",
contentType: "text/csv",
payload: csv,
headers: { Authorization: "Bearer " + token },
muteHttpExceptions: true,
});
if (resp.getResponseCode() >= 300) {
throw new Error("GCS upload failed: " + resp.getResponseCode() + " " + resp.getContentText());
}
const meta = JSON.parse(resp.getContentText());
Logger.log("Uploaded gs://%s/%s generation=%s", BUCKET, meta.name, meta.generation);
}
function csvEscape_(cell) {
const s = String(cell == null ? "" : cell);
if (/[",\n]/.test(s)) return '"' + s.replace(/"/g, '""') + '"';
return s;
}- Line 7: Target GCS bucket name (not gs:// prefix).
- Line 16: Uses formatted cell text for CSV export.
- Line 18: RFC-style escaping for commas and quotes.
- Line 21: Timestamped object path under exports/.
- Line 24: Simple media upload for the raw CSV bytes.
- Line 4: Bearer token for the current script identity.
- Line 33: Lets you read error bodies instead of throwing early.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: GCS upload
- 1Bucket exists in the correct GCP project
- 2oauthScopes includes devstorage.read_write (or cloud-platform)
- 3Running user has storage.objects.create on the bucket
- 4ExportReady sheet is the intended export
- 5OBJECT_PREFIX ends with / if you want folder semantics
- 6Re-authorize after changing scopes
Frequently asked questions
Missing scope in appsscript.json, stale authorization, or IAM denied. Re-run from the editor after scope changes and check bucket permissions.
Yes if the installing account has bucket access. For least privilege, switch to a service-account token.
contentType: 'text/csv' in the UrlFetch options is sent as the object content type.
Media upload suits modest CSVs. For large files, use resumable upload sessions against the same API.
No. ACL/IAM stay private unless you change bucket policy separately.
Uploading the same name creates a new generation if versioning is on, or replaces the live object if not.
DriveApp.getFileById(...).getBlob() as payload with the blob content type.