Skip to content

Backup CSV files using Google Spreadsheets

Ballerina is an open-source programming language that empowers developers to integrate their system easily with the support of connectors. In this guide, we are mainly focusing on how to use Google Spreadsheets Connector to backup CSV (Comma Separated Value) files in Google Spreadsheets. You can find other integration modules from the wso2-ballerina GitHub repository.

What you'll build

The following diagram illustrates the scenario:

Message flow diagram image

Let's consider a scenario where a CSV file is uploaded to a newly created Google Spreadsheet. A new sheet is created in the spreadsheet with the current date as the sheet name. CSV content is added to the new sheet. The sheet is then read and the content is displayed on the console to the user.

Before you begin

  • Download and install Ballerina Integrator for your operating system

    Note: This installs Ballerina language version 1.0.1, a compiler plugin to help mitigate errors, and snippet .jar files for custom auto-completion.

  • Install Oracle JDK 1.8.*
  • Install VS Code

    Tip: For a better development experience, use VS Code (which is the recommended editor for Ballerina Integrator).

  • Install the Ballerina Integrator Extension from the VS Code marketplace

  • You need to obtain credentials to access Google Spreadsheet in order to configure a Google Spreadsheet client. Instructions on how to obtain the credentials for the Google Spreadsheet can be found here.

Get the code

You can download the ZIP file and extract the contents to get the project code.

Download ZIP

Implementation

The Ballerina project is created for the integration use case as explained above. Please follow the steps given below. You can learn about the Ballerina project and module in this link.

1. Create a new project.

  $ ballerina new using-google-spreadsheets-connector

2. Create a module.

  $ ballerina add backup_csv_files_using_gsheets4

To implement the scenario in this guide, you can use the following package structure:

  using-google-spreadsheets-connector
  ├── Ballerina.toml
  └── src
      └── backup_csv_files_using_gsheets4
          ├── Module.md
          └── uploader.bal

3. Add the project configuration file

Add the project configuration file by creating a ballerina.conf file under the root path of the project structure. The configuration file must have the following configurations

ACCESS_TOKEN="<Google Spreadsheet Access Token>"
CLIENT_ID="<Google Spreadsheet Client Id>"
CLIENT_SECRET="<Google Spreadsheet Client Secret>"
REFRESH_URL="<Refresh URL>"
REFRESH_TOKEN="<Refresh Token>"

4. Write the integration.

Take a look at the code samples below to understand how to implement the integration scenario.

uploader.bal

The following code reads the contents of a CSV file and saves the data in a new Google Spreadsheet. The data is then read from the spreadsheet and displayed on the console.

import ballerina/io;
import wso2/gsheets4;
import ballerina/time;
import ballerina/log;
import ballerina/config;

// Define a Spreadsheet client
gsheets4:SpreadsheetConfiguration spreadsheetConfig = {
    oAuthClientConfig: {
        accessToken: config:getAsString("ACCESS_TOKEN"),
        refreshConfig: {
            clientId: config:getAsString("CLIENT_ID"),
            clientSecret: config:getAsString("CLIENT_SECRET"),
            refreshUrl: gsheets4:REFRESH_URL,
            refreshToken: config:getAsString("REFRESH_TOKEN")
        }
    }
};
gsheets4:Client spreadsheetClient = new(spreadsheetConfig);

public function main() {
    string csvFilePath = "src/backup_csv_files_using_gsheets4/resources/people.csv";
    string spreadSheetName = "People_Info";
    string sheetName = getCurrentDateName();
    string topLeftCell = "A1";

    // Create a Readable CSV Channel
    io:ReadableCSVChannel|error readableCsvChannelOrError = io:openReadableCsvFile(csvFilePath);
    if (readableCsvChannelOrError is error) {
        log:printError("An error occurred opening the Readable CSV Channel: ", err = readableCsvChannelOrError);
        return;
    }
    io:ReadableCSVChannel readableCsvChannel = <io:ReadableCSVChannel>readableCsvChannelOrError;

    // Store CSV data from file to a String array
    string[][] csvData = getCsvFromFile(readableCsvChannel);

    //Create a GoogleSheet using the Spreadsheet client
    gsheets4:Spreadsheet spreadsheet = checkpanic spreadsheetClient->createSpreadsheet(spreadSheetName);
    string spreadsheetId = checkpanic <@untainted>spreadsheet.getSpreadsheetId();

    //Add a new sheet
    _ = checkpanic spreadsheetClient->addNewSheet(spreadsheetId, sheetName);

    // Backup CSV Data to the new sheet
    boolean successful = checkpanic spreadsheetClient->setSheetValues(spreadsheetId,
        sheetName, csvData, topLeftCell, findBottomRightCell(csvData));

    if (successful) {
        log:printInfo("Successfully backedup the CSV file.");
    }

    // Close the Readable CSV Channel
    closeReadableCSVChannel(readableCsvChannel);

    // Get the values from the spreadsheet
    string[][] spreadsheetData = checkpanic spreadsheetClient->getSheetValues(spreadsheetId, sheetName, topLeftCell,
                                                findBottomRightCell(csvData));

    displaySpreadsheetData(spreadsheetData);
}

// Function that iterates through the obtained data from the spreadsheet
function displaySpreadsheetData(string[][] spreadsheetData) {
    log:printInfo("**************** CSV Entries ****************");
    foreach var row in spreadsheetData {
        foreach var entry in row {
            log:printInfo(entry + "\t");
        }
        log:printInfo("\n");
    }
    log:printInfo("*********************************************");
}

// Returns the current date to be used as the spreadsheet name
function getCurrentDateName() returns string {
    time:Time time = time:currentTime();
    int year = time:getYear(time);
    int month = time:getMonth(time);
    int day = time:getDay(time);
    return year.toString() + "_" + month.toString() + "_" + day.toString();
}

// Read CSV data from the file
function getCsvFromFile(io:ReadableCSVChannel csvChannel) returns string[][] {
    string[][] csvData = [];
    while (csvChannel.hasNext()) {
        string[]|error? records = csvChannel.getNext();
        if (records is string[]) {
            csvData.push(records);
        } else {
            log:printError("Error occurred while reading the CSV channel: ", err = records);
        }
    }
    return csvData;
}

function findBottomRightCell(string[][] csvData) returns string {
    int rowCount = csvData.length();
    int columnCount = 0;
    if (rowCount > 0) {
        columnCount = csvData[0].length();
    }
    return convColNumToChars(columnCount) + rowCount.toString();
}

function closeReadableCSVChannel(io:ReadableCSVChannel csvChannel) {
    var result = csvChannel.close();
    if (result is error) {
        log:printError("Error occurred while closing the channel: ", err = result);
    }
}

function convColNumToChars(int columnNumber) returns string {
    if (columnNumber != 0) {
        return convColNumToChars((columnNumber - 1) / 26) + genAsciiChar(columnNumber % 26);
    } else {
        return "";
    }
}

function genAsciiChar(int charCode) returns string {
    string[] charSet = ["Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O",
        "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y"];
    return charSet[charCode];
}

Testing

To build the module, navigate to the project root directory and execute the following command.

  ballerina build backup_csv_files_using_gsheets4

This command creates the executable jar file.

Now run the backup_csv_files_using_gsheets4.jar file created in the above step.

  java -jar target/bin/backup_csv_files_using_gsheets4.jar

This starts the service that reads the data from the people.csv files stored in the resources folder. You will notice that the contents of the file have been set in the Google Spreadsheet and the data will also display on the console.

Top