使用谷歌应用程序脚本修改BigQuery数据

function getService() {
  var jsonKey = JSON.parse(PropertiesService.getScriptProperties().getProperty('jsonKey'));
  var privateKey = jsonKey.private_key;
  var serviceAccountEmail = jsonKey.client_email;

  var config = {
    'type': 'service_account',
    'project_id': jsonKey.project_id,
    'private_key_id': jsonKey.private_key_id,
    'private_key': privateKey,
    'client_email': serviceAccountEmail,
    'client_id': jsonKey.client_id,
    'auth_uri': 'https://accounts.google.com/o/oauth2/auth',
    'token_uri': 'https://oauth2.googleapis.com/token',
    'auth_provider_x509_cert_url': 'https://www.googleapis.com/oauth2/v1/certs',
    'client_x509_cert_url': jsonKey.client_x509_cert_url
  };

  var service = OAuth2.createService('BigQuery')
    .setAuthorizationBaseUrl(config.auth_uri)
    .setTokenUrl(config.token_uri)
    .setClientId(config.client_id)
    .setClientSecret(null)
    .setScope('https://www.googleapis.com/auth/bigquery')
    .setPrivateKey(privateKey)
    .setIssuer(serviceAccountEmail)
    .setSubject(null)
    .setPropertyStore(PropertiesService.getScriptProperties());

  if (!service.hasAccess()) {
    Logger.log(service.getAuthorizationUrl());
    return null;
  }

  return service;
}

function updateBigQueryData() {
  var service = getService();
  if (!service) {
    Logger.log('Authentication failed.');
    return;
  }

  var projectId = 'your-project-id';
  var datasetId = 'your-dataset-id';
  var tableId = 'your-table-id';

  var sqlQuery = 'UPDATE `' + projectId + '.' + datasetId + '.' + tableId + '` SET column1 = "newValue" WHERE condition';

  var request = {
    'query': sqlQuery,
    'useLegacySql': false
  };

  var response = BigQuery.Jobs.query(request, projectId);
  Logger.log('Query executed successfully: ' + JSON.stringify(response));
}