Google Cloud Storage

You can upload one or more CSV files to a specific bucket in Google Cloud Storage and then use Google Apps Script to import the CSV files from Cloud Storage into your Google Cloud SQL database.

In the method here, the CSV file is deleted from Cloud Storage after the import operation is complete. You can however call the /copyTo/ endpoint to move the CSV files into another Cloud Storage folder after processing.

It is important to add a wait (sleep) function because the API will throw an error if you begin uploading another file while the previous import operation is pending. The file names must be encoded as well.


function uploadtoCloudSQL() {

  // Written by Amit Agarwal amit@labnol.org
  // Web: www.ctrlq.org

  var service = getService();

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

  var token = service.getAccessToken();

  // Getting list of files to be processed
  var result = JSON.parse(
    UrlFetchApp.fetch('https://www.googleapis.com/storage/v1/b/BUCKET_NAME/o', {
      method: "GET",
      headers: {
        Authorization: 'Bearer ' + token
      }
    }).getContentText());

  for (var i = 0; i < result.items.length; i++) {
    if (result.items[i].name.indexOf(".") !== -1) {
      files.push(result.items[i].name);
    }
  }

  for (var f = 0; f < files.length; f++) {

    var path = files[f].split("/");

    var payload =
      '{"importContext" : { "csvImportOptions": {"table":"MY_TABLE"}, "fileType": "CSV", "database": "MY_DATABASE", "uri": "gs://BUCKET_NAME/FOLDER/CSVFILE"}}'
      .replace("FOLDER", path[0])
      .replace("CSVFILE", path[1]);

    UrlFetchApp.fetch('https://www.googleapis.com/sql/v1beta4/projects/PROJECT/instances/INSTANCE/import', {
      method: "POST",
      contentType: "application/json",
      headers: {
        Authorization: 'Bearer ' + token
      },
      payload: payload,
      muteHttpExceptions: true
    });

    UrlFetchApp.fetch('https://www.googleapis.com/storage/v1/b/BUCKET_NAME/o/' + encodeURIComponent(files[f]), {
      method: "DELETE",
      headers: {
        Authorization: 'Bearer ' + token
      }
    });

    // Wait for the previous import job to end
    Utilities.sleep(5000);

  }

}

One of my recent project involved uploading files from Google Drive to a particular bucket in Google Cloud Storage. The front-end was created in a Google Spreadsheet where the full Google Drive path of the file is specified along with the destination bucket / folder in Google Cloud Storage.

The Google Apps Script would then automatically upload the files from Drive to Cloud Storage via a time-based trigger or can be run manually as well.

To get started, you need to go to the Google Developer’s console and enable the Google Cloud Storage API and the Google Cloud Storage JSON Data API. Also enable billing for your account to access Google Cloud Storage. Then create the API keys (OAuth 2, Web Application) with the redirect URL containing your Google Apps Script project key. The OAuth2 library for Apps Script should also be included in your project.

var params = {
  CLIENT_ID: 'xxx.apps.googleusercontent.com',
  CLIENT_SECRET: 'xyz',
  BUCKET_NAME: 'ctrlq-bucket',
  FILE_PATH: 'folder_name/filename',
  DRIVE_FILE: 'fileID'
};

/* Written by Amit Agarwal */
/* Email: amit@labnol.org   Web: ctrlq.org */

function uploadFileToGCS() {

  var service = getService();
  if (!service.hasAccess()) {
    Logger.log("Please authorize %s", service.getAuthorizationUrl());
    return;
  }

  var blob = DriveApp.getFileById(params.DRIVE_FILE).getBlob();
  var bytes = blob.getBytes();

  var url = 'https://www.googleapis.com/upload/storage/v1/b/BUCKET/o?uploadType=media&name=FILE'
    .replace("BUCKET", params.BUCKET_NAME)
    .replace("FILE", encodeURIComponent(params.FILE_PATH));

  var response = UrlFetchApp.fetch(url, {
    method: "POST",
    contentLength: bytes.length,
    contentType: blob.getContentType(),
    payload: bytes,
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    }
  });

  var result = JSON.parse(response.getContentText());
  Logger.log(JSON.stringify(result, null, 2));
}

function getService() {
  return OAuth2.createService('ctrlq')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId(params.CLIENT_ID)
    .setClientSecret(params.CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('https://www.googleapis.com/auth/devstorage.read_write')
    .setParam('access_type', 'offline')
    .setParam('approval_prompt', 'force')
    .setParam('login_hint', Session.getActiveUser().getEmail());
}

function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Connected to Google Cloud Storage');
  } else {
    return HtmlService.createHtmlOutput('Access Denied');
  }
}

/* For support and customization, see www.ctrlq.org */