AppScript function for exporting Google Sheets table to BigQuery

exportSheetToBigQuery.js

function exportSheetToBigQuery() {
  /** =========================
   *  configuration
   *  ========================= */
  const CONFIG = {
  PROJECT_ID: 'project_id',
  DATASET_ID: 'dataset_id',
  TABLE_ID:   'table_id',
  SHEET_NAME: 'export_to_bq',     
  HEADER_ROW: 1,            
  BATCH_SIZE: 500,          
  };

  const sheet = getSheet_(CONFIG.SHEET_NAME);
  const values = getSheetValues_(sheet, CONFIG.HEADER_ROW);
  if (!values || values.length < 2) throw new Error('no data rows found under the header row');

  // keep header names exactly as on the sheet
  const headers = values[0].map(h => String(h).trim());
  const rows = values.slice(1);
  const schema = inferSchema_(headers, rows);

  ensureDataset_(CONFIG.PROJECT_ID, CONFIG.DATASET_ID);
  createOrReplaceTable_(CONFIG.PROJECT_ID, CONFIG.DATASET_ID, CONFIG.TABLE_ID, schema);

  const insertErrors = streamRows_(CONFIG.PROJECT_ID, CONFIG.DATASET_ID, CONFIG.TABLE_ID, headers, rows, CONFIG.BATCH_SIZE);
  if (insertErrors.length) {
    Logger.log(JSON.stringify(insertErrors.slice(0, 3), null, 2));
    throw new Error('one or more insert batches had errors. see logs for details');
  }

  Logger.log(`done. inserted ${rows.length} rows into ${CONFIG.PROJECT_ID}.${CONFIG.DATASET_ID}.${CONFIG.TABLE_ID}`);
}

/** --- sheets helpers --- */
function getSheet_(name) {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(name);
  if (!sheet) throw new Error(`sheet not found: ${name}`);
  return sheet;
}

function getSheetValues_(sheet, headerRow) {
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  if (lastRow < headerRow) return [];
  return sheet.getRange(headerRow, 1, lastRow - headerRow + 1, lastCol).getValues();
}

/** --- schema inference --- */
function inferSchema_(headers, rows) {
  const sample = rows.slice(0, Math.min(rows.length, 2000));
  const fields = headers.map((name, idx) => {
    const type = guessType_(sample.map(r => r[idx]));
    return { name: name, type, mode: 'NULLABLE' };
  });
  return { fields };
}

function guessType_(values) {
  const nonEmpty = values.filter(v => v !== '' && v !== null && v !== undefined);
  if (nonEmpty.length === 0) return 'STRING';

  if (nonEmpty.every(v => v instanceof Date)) return 'DATE';

  const testers = [
    { type: 'BOOLEAN',   fn: v => typeof v === 'boolean' || /^true|false$/i.test(String(v).trim()) },
    { type: 'INTEGER',   fn: v => /^-?\d+$/.test(String(v).trim()) },
    { type: 'FLOAT',     fn: v => /^-?\d+(\.\d+)?([eE]-?\d+)?$/.test(String(v).trim()) },
    { type: 'DATE',      fn: v => /^\d{4}-\d{2}-\d{2}$/.test(String(v).trim()) },
    { type: 'TIMESTAMP', fn: v => /^\d{4}-\d{2}-\d{2}([ T]\d{2}:\d{2}:\d{2}(\.\d{1,6})?([zZ]|([+\-]\d{2}:\d{2}))?)$/.test(String(v).trim()) }
  ];

  for (const t of testers) {
    if (nonEmpty.every(t.fn)) return t.type;
  }
  return 'STRING';
}

/** --- bigquery admin --- */
function ensureDataset_(projectId, datasetId) {
  try {
    BigQuery.Datasets.get(projectId, datasetId);
  } catch (e) {
    const resource = { datasetReference: { datasetId, projectId }, location: 'EU' };
    BigQuery.Datasets.insert(resource, projectId);
  }
}

/** create or replace table using ddl built from inferred schema */
function createOrReplaceTable_(projectId, datasetId, tableId, schema) {
  const ddl = schemaToDdl_(projectId, datasetId, tableId, schema);
  runQuerySync_(projectId, ddl);
}

function schemaToDdl_(projectId, datasetId, tableId, schema) {
  const colDefs = schema.fields.map(f => `\`${f.name}\` ${bqTypeSql_(f.type)}`).join(', ');
  return `CREATE OR REPLACE TABLE \`${projectId}.${datasetId}.${tableId}\` (${colDefs})`;
}

function bqTypeSql_(t) {
  switch (t) {
    case 'INTEGER':   return 'INT64';
    case 'FLOAT':     return 'FLOAT64';
    case 'BOOLEAN':   return 'BOOL';
    case 'STRING':    return 'STRING';
    case 'DATE':      return 'DATE';
    case 'TIMESTAMP': return 'TIMESTAMP';
    default:          return 'STRING';
  }
}

/** run a synchronous query job (for ddl, truncate, etc.) */
function runQuerySync_(projectId, query) {
  const req = { configuration: { query: { query, useLegacySql: false } } };
  const job = BigQuery.Jobs.insert(req, projectId);
  const jobId = job.jobReference.jobId;

  while (true) {
    const cur = BigQuery.Jobs.get(projectId, jobId);
    if (cur.status.state === 'DONE') {
      if (cur.status.errorResult) {
        throw new Error(`query failed: ${JSON.stringify(cur.status.errors)}`);
      }
      break;
    }
    Utilities.sleep(800);
  }
}

/** --- data insert --- */
function streamRows_(projectId, datasetId, tableId, headers, rows, batchSize) {
  const errors = [];
  for (let i = 0; i < rows.length; i += batchSize) {
    const slice = rows.slice(i, i + batchSize);
    const insertReq = {
      kind: 'bigquery#tableDataInsertAllRequest',
      ignoreUnknownValues: true,
      skipInvalidRows: false,
      rows: slice.map(r => ({
        insertId: Utilities.getUuid(),
        json: rowToObject_(headers, r)
      }))
    };

    const res = BigQuery.Tabledata.insertAll(insertReq, projectId, datasetId, tableId);
    if (res && res.insertErrors && res.insertErrors.length) {
      errors.push({ batchStart: i, batchEnd: i + slice.length - 1, insertErrors: res.insertErrors });
      Logger.log(JSON.stringify(res.insertErrors.slice(0, 5), null, 2));
    }
  }
  return errors;
}

function rowToObject_(headers, row) {
  const obj = {};
  for (let c = 0; c < headers.length; c++) {
    // keep the original header name as the key
    const key = headers[c];
    const v = row[c];

    if (v === '' || v === null || v === undefined) { obj[key] = null; continue; }

    if (v instanceof Date) {
      obj[key] = formatDateYMD_(v);
      continue;
    }

    if (typeof v === 'boolean' || typeof v === 'number') { obj[key] = v; continue; }

    if (typeof v === 'string') {
      const s = v.trim();
      obj[key] = s === '' ? null : s;
      continue;
    }

    obj[key] = v;
  }
  return obj;
}

/** format a Date to YYYY-MM-DD (UTC) */
function formatDateYMD_(d) {
  return Utilities.formatDate(d, 'UTC', 'yyyy-MM-dd');
}