Google Cloud CSV to Google Sheets

5 min readMar 15, 2021

How-To Sync a CSV file from GCP to Google Sheets

I was tasked with syncing data from a Google Cloud Bucket to Google Sheets. As a cloud novice, I naively presumed Google would have some mechanism to directly sync from Cloud to the Workplace. Upon further research into Google-based solutions, I realized no direct sync existed. After playing with various Google APIs, Scripts, CloudFunctions, and with little JavaScript experience; a JS script was compiled to sync file data from a cloud bucket to a spreadsheet in Google Drive.

Essentially, there are two main components to this: the Cloud (GCP) and Google Workspace. In this article, I will be going over how I set up my Cloud environment as well as Google Apps Script. As Google Apps Script resides in the workspace, it can be tied to a Google Cloud Project. This is an optimal resource that ties the two together. This script retrieves a CSV file from a GCP Bucket, parses CSV data, clears the designated spreadsheet, and finally writes the data to the sheet.

Project Requirements:

Google Cloud Project Access (Bucket)
OAuth WebApp Client Account
IAM Permission to Read/Write from Cloud Storage Bucket
Write Access to Sheets

Setting Up Google Apps Script:

1. Go to Project Settings and Input GCP Project Number

2. Add the Google Apps Script OAuth Library to the Libraries Tab. Copy the latest Script ID can be found here.

3. Add the following APIs to the Services Tab: Google Sheets API and Google Drive API.

Setting Up GCP:

In GCP, Under API’s & Services, ensure the OAuth Consent Screen is configured for the purpose of your use. For reference
Create a Web Application OAuth Account. Download and Save Credentials
Attach the Web Application Account to the Google Cloud Bucket with read/write permission.
Enable the Google Sheets API, Cloud Logger API, Google Drive API, and App Script API.
After running the code the first, it will authenticate as the user running the code. Ensure the redirect URL depicted in the Authorization Screen is entered into the service account’s scope.

<Script>

//----Input Web Application Credentials and Bucket/File----
var params = {
  CLIENT_ID: 'X',
  CLIENT_SECRET: 'X',
  BUCKET_NAME: 'X',
  FILE_PATH: 'X.csv'
};function CSVtoSheet() {
  var service = getService();
  if (!service.hasAccess()) {
    Logger.log(service.getAuthorizationUrl());
    return;
  }//----Get File----
var url = 'https://storage.googleapis.com/storage/v1/b/BUCKET_NAME/o/OBJECT_NAME?alt=media'
    .replace("BUCKET_NAME", params.BUCKET_NAME)
    .replace("OBJECT_NAME", encodeURIComponent(params.FILE_PATH));var response = UrlFetchApp.fetch(url, {
    method: "GET", 
    headers: { Authorization: 'Bearer ' + service.getAccessToken()}
    });//----CSV Parse----
var file_csv = Utilities.parseCsv(response)
var rowValues = file_csv;//----Spreadsheet Row Clear/Write----
var file_write = {
    'valueInputOption': 'USER_ENTERED',
    'data': [{
        'range': 'SpreadsheetTabName!A1:L50',
        'majorDimension': 'ROWS',
        'values': rowValues
      }]};var file_clear = {"spreadsheetId": 'X',"ranges": ['SpreadsheetTabName!A1:L50']};var cleared = Sheets.Spreadsheets.Values.batchClear(file_clear, 'SpreadsheetID');
   Logger.log(cleared);var wrote = Sheets.Spreadsheets.Values.batchUpdate(file_write, 'SpreadsheetID');
  Logger.log(wrote);
}//----Authorization----
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.full_control')
    .setScope('https://www.googleapis.com/auth/drive')
    .setParam('access_type', 'offline')
    .setParam('approval_prompt', 'force')
    .setParam('login_hint', Session.getActiveUser().getEmail());}
function authCallback(request) {
  var service = getService();
  var ifauthorized = service.handleCallback(request);
  if (ifauthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

Code Explained

// — — Input Web Application Credentials and Bucket/File — —

From the .JSON credentials file of the web application account created, copy and paste the client_id and client_secret (leave as string — replacing the X). In GCP, enter the name of the bucket in which the file resides. For file_path, enter the file name or the path to filename ie: x/y/z/file.csv.

// — — Get File — —

Using the authorization token, it will “get” the file from the bucket specified above. This is utilizing the JSON API.

// — — CSV Parse — —

It got the file, now it will parse that file to get row-by-row data.

// — — Spreadsheet Row Clear/Write — —

Now for the fun part! 🎉

var file_write: for range- change SpreadsheetTabName to the name of the tab to which the data is being synced to. The range A1:L50 can also be changed to suit your purposes.
var file_clear: match the SpreadsheetTabName and range to what is specified in file_write.
var cleared: Input the ID of the spreadsheet. The tab and range mentioned in file_clear will clear the data in the spreadsheet. This will not clear any formatting such as highlights etc. that you may have.
var wrote: Input the same ID of the spreadsheet in var cleared. It will now write your data!
The Logger.log() is vital for troubleshooting. Additionally, it is mesmerizing watching it run through the code.

// — — Authorization — —

This is the authorization script found from Google OAuth. The scopes can be changed. I have it set to devstorage.full_control.

Final Word

There are other solutions that perform the same functionalities as this script. This script allows for the spreadsheet to not have to be “shared” with the service account writing the data. Therefore, if there are domain sharing restrictions, or if the spreadsheet cannot be made public — this script serves to uphold both requirements and keep the data internally flowing from Cloud to Workspace. Lastly, as this script is in the workspace — it can be easily shared, triggered, and monitored.