bigquery-appscript.js

function exportToBigQuery() {

    var projectId = 'learn-big-query-307011'; //GCP Project ID here
    var datasetId = 'ga_gbq_data_pipeline_demo'; //GBQ Dataset ID
    var tableId = 'ga_codechilli_users_by_country'; //GBQ Table ID

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var values = spreadsheet.getSheetByName('data').getDataRange().getValues();
    var csv = generateCSV(values);

    var job = {
        configuration: {
            load: {
                destinationTable: {
                    projectId: projectId,
                    datasetId: datasetId,
                    tableId: tableId
                },
                skipLeadingRows: 1,
                schema: {
                    fields: [{ //Configure the schema identical to GBQ table schema
                            name: 'Date',
                            type: 'DATE',
                            mode: 'NULLABLE'
                        },
                        {
                            name: 'Country',
                            type: 'STRING',
                            mode: 'NULLABLE'
                        },
                        {
                            name: 'Users',
                            type: 'NUMERIC',
                            mode: 'NULLABLE'
                        },
                    ]
                }
            }
        }

    };

    var data = Utilities.newBlob(generateCSV(values), 'text/csv').setContentType('application/octet-stream');

    BigQuery.Jobs.insert(job, projectId, data);
}

function generateCSV(values) {
    var csv = '';

    for (var row = 0; row < values.length; row++) {
        for (var col = 0; col < values[row].length; col++) {
            if (values[row][col] instanceof Date) {
                values[row][col] = values[row][col].toISOString().split('T')[0];
            }

            if (values[row][col].toString().indexOf(',') !== -1) {
                values[row][col] = '"' + range[row][col] + '"';
            }
        }

        if (row < values.length - 1) {
            csv += values[row].join(',') + '\r\n';
        } else {
            csv += values[row];
        }
    }

    return csv;
}