Kotlin DSL for Google Sheets

Introduction

Most software developers are specialized in one or two general-purpose programming languages, like Java, C, or PHP. This technology separates them unequivocally across companies, job positions, projects, and teams with which they can cooperate. But surprisingly despite this strict division, there are languages commonly used by all of them (and I’m not talking about English). After all, every developer sometimes needs to define a pattern for text validation, query database, or set up a build script. And he mostly does that by using regexp, SQL, and Maven/Gradle languages. But there are no job offers for roles like Regexp Developer, Gradle Engineer or PlantUML Guru. Does it mean that these technologies are just syntactic sugar and at any point in time we can get rid of them painlessly?

If you want to answer yes, think twice and figure out how will you make a Java developer implement email verification without using regular expressions. He only needs to convert this regexp to Java:

/[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*
 @(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?/g

Good luck! 😜

Or maybe you are crazy enough to ask C# engineer to query Microsoft SQL Server database without using SQL expressions?

No way!

Fulfilling software requirements to be effective often needs to harness more than just one programming language. Usually, the solution relies on some general-purpose language in accompaniment with languages solving some domain-specific tasks.

DSL? Are you going to show off your vintage router?

Not this time. DSL, meaning “domain-specific language”

We use them every day to solve specific problems, such as database querying, finding matches in the text, or building process descriptions. To name a few:

1. PlantUML,
2. sed,
3. HTML,
4. BPEL,
5. XPath,
6. Make,
7. LaTeX,
8. MATLAB

In this article, I would like to show how we can quickly design our own custom DSL for real-world domain. Thanks to Kotlin‘s flexibility and its specific set of features this task should be a piece of cake (a tasty one 🍰).

Problem

There was a time when I had to generate Excel spreadsheets from Java/Kotlin code. Unfortunately, it sparked exponential raise of spaghetti code after each new business requirement came. I was pretty close then to writing my own DSL as a replacement for the pasta code. Luckily I came across a pretty neat library which rid me of my misery. Implementing another ExcelDSL library turned out to be senseless then.

Now, pulled by ambition, I decided to develop Google Sheets DSL equivalent library named imaginatively: SheetsDSL.

Expected Result

As the DSLs for the same domain problem can be designed in a very different way, I would like to follow one concrete implementation, so I choose a simple example from the ExcelDSL library as my reference point. After small correction, this is the way in which I would like to use my SheetsDSL lib:

val spreadsheet: Spreadsheet = spreadsheet {
    sheet {
        row {
            cell("Hello")
            cell("World!")
        }
        row(2)
        row {
            emptyCell(3)
            cell("Here!")
        }
    }
}

After opening this spreadsheet in a browser

Desktop.getDesktop().browse(URI.create(spreadsheet.spreadsheetUrl))

we should expect a brand-new document in our personal space:

Let’s do it

Project initialization

Following this guide:

1. Create a simple project using IntelliJ‘s wizard:

and add Google libraries to the build.gradle.kts file:

// ....
implementation("com.fasterxml.jackson.core:jackson-databind:2.13.4.2")
implementation("com.google.api-client:google-api-client:2.0.0")
implementation("com.google.oauth-client:google-oauth-client-jetty:1.34.1")
implementation("com.google.apis:google-api-services-sheets:v4-rev20220927-2.0.0")
// ....

2. The most unpleasant part. Hope this doesn’t cool your attitude.

• Create a new project in the Google Cloud Console

• enable Google Sheets API
• configure OAuth consent screen
  • choose the External user type
  • fill with your data
  • save and continue with default values on the next pages
  • click the PUBLISH APP button

• create OAuth client ID
  • choose Desktop app
  • save JSON as a credentials.json file in yours project root resources’ directory
• add this code to easily authorize your project to manipulate spreadsheets in your Google account

object SheetsService {

    private val httpTransport by lazy { GoogleNetHttpTransport.newTrustedTransport() }
    private val jsonFactory: JsonFactory by lazy { GsonFactory.getDefaultInstance() }
    private val credential by lazy { credential(httpTransport) }

    private val sheets: Sheets by lazy {
        Sheets.Builder(httpTransport, jsonFactory, credential)
            .setApplicationName("SheetsDSL")
            .build()
    }

    private fun credential(httpTransport: NetHttpTransport): Credential {
        val credentialUrl = Resources.getResource(
            SheetsService::class.java, "/credentials.json"
        )

        val clientSecrets = GoogleClientSecrets.load(
            jsonFactory, credentialUrl.openStream().bufferedReader()
        )

        val flow = GoogleAuthorizationCodeFlow.Builder(
            httpTransport, jsonFactory, clientSecrets, listOf(SPREADSHEETS)
        )
            .setDataStoreFactory(FileDataStoreFactory(File("tokens")))
            .setAccessType("offline")
            .build()

        val receiver = LocalServerReceiver.Builder().setPort(8888).build()

        return AuthorizationCodeInstalledApp(flow, receiver).authorize("user")
    }
}

3. Smoke test. Try to create an empty spreadsheet

• add this method to SheetService object:

fun create(spreadsheet: Spreadsheet): Spreadsheet =
    sheets.spreadsheets().create(spreadsheet).execute()

it will allow us quickly initiate Sheets class – a core of Google Sheets API for spreadsheets manipulation

• and execute

fun main() {
    print(SheetsService.create(Spreadsheet()))
}

• at the first run you will be prompted to allow SheetDSL manipulate your files
  • because we didn’t finish verification process, you may encounter this warning
  • if this process will succeed, authorization info for future API calls will be saved in the tokens folder

• check if the just-created spreadsheet has been printed in the console

Implementation

• I’ve chosen a naive but bulletproof approach for creating spreadsheets:
  1. Model a spreadsheet using helper DSL classes and functions.
     • Just like shown in the Expected Result part.
  2. Build from it a new instance of the Google API’s Spreadsheet class.
  3. Call the API’s create endpoint with the previous step outcome as an argument.
• To distinguish new classes from those existing in imported libraries I will add the Dsl suffix to their names.
• e.g. SheetDsl, CellDsl
• Heavily inspired by ExcelDSL library:
• Customization of Spreadsheet elements is done by running provided init function in their context.

class RowDsl {

    fun cell(value: String, init: CellDsl.() -> Unit): CellDsl {
        val cellDsl = CellDsl(value)
        cellDsl.init()
        return cellDsl
    }

    // ...
}

• Calling the build method on a class modeling a Google Sheet’s entity returns a new instance of it.

Google Sheets API

For a better class structure understanding I prepared a simplified UML graph:

Base solution

To satisfy the requirements described in the Expected Result part, my implementation will follow the bottom-up approach.

1. CellData model:

class CellDsl(
    private val value: String? = null,
) {
    internal fun build() = CellData().apply {
        userEnteredValue = when (value) {
            null, "" -> null
            else -> ExtendedValue().setStringValue(value.toString())
        }
    }
}

2. RowData with methods creating cells:

class RowDsl(val height: Int? = null) {

    private val cells = mutableListOf<CellDsl>()

    fun cell(value: String = "", init: CellDsl.() -> Unit = {}) =
        CellDsl(value).apply(init).also { cells += it }

    fun emptyCell(count: Int = 1) {
        if (count < 1) return
        repeat(count) { cell() }
    }

    internal fun build() = RowData().apply {
        setValues(cells.map { it.build() })
    }
}

3. Simple Sheet model:

class SheetDsl {

    private val rows = mutableListOf<RowDsl>()

    fun row(rowCount: Int = 1, init: RowDsl.() -> Unit = {}) {
        repeat(rowCount) {
            rows += RowDsl().apply(init)
        }
    }

    fun build() = Sheet().apply {
        data = listOf(GridData().apply {
            rowData = rows.map { it.build() }
        })
    }
}

4. And the top Spreadsheet class:

class SpreadsheetDsl {

    private val sheetList = mutableListOf<SheetDsl>()

    fun sheet(init: SheetDsl.() -> Unit = {}) {
        sheetList += SheetDsl().apply(init)
    }

    internal fun build() = Spreadsheet().apply {
        sheets = sheetList.map { it.build() }
    }
}

fun spreadsheet(
    init: SpreadsheetDsl.() -> Unit,
): Spreadsheet =
    SheetsService.create(SpreadsheetDsl().apply(init).build())

This fulfills the given requirement and allows basic spreadsheet creation.

However, I hope that the reader’s ambitions are much greater than that, and he will be keen to explore more advanced concepts described in the rest of the article.

Complex Example

As a showcase of Kotlin‘s flexibility and the variety of ways a DSL can be designed I prepared a more complex example:

al spreadsheet = spreadsheet("Generated Spreadsheet") {
    sheet("First") {
        row {
            cell("Bold Roboto") {
                font {
                    fontFamily = "Roboto"
                    bold = true
                }
            }

            cell("White on red and aligned to right") {
                font {
                    color = WHITE
                }
            } bg RED align RIGHT

            +"Am I rotated?" % 45
        }

        columnWidth(1, 240)

        row(cellCount = 2) {
            cell("4 * 84 =") {
                horizontalAlignment = RIGHT
            }
            cellFormula("= 4 * 84") {
                backgroundColor = GRAY
            }
        }

        row()

        row {
            emptyCell()
            cell(4234.234) {
                border(DOTTED, BLUE, TOP_BOTTOM)
            }
        }
    }

    sheet("second - empty")

    sheet {
        columnWidth(0..2, 150)
        columnWidth(3, 60)

        row(cellCount = 3) {
            cell("thin") {
                font {
                    bold = true
                }
                horizontalAlignment = CENTER
            }
        }

        row(2)

        row(height = 63) {
            cell("tall") {
                font {
                    bold = true
                }
            } align CENTER vAlign MIDDLE
        }
    }
}

Which creates:

Bellow explanation of the used concepts.

Higher Order Functions (docs)

A higher-order function is a function that takes functions as parameters, or returns a function.

val spreadsheet = spreadsheet("Generated Spreadsheet") {
    sheet("First") {
        row {
            cell("Bold Roboto") {
                font {
                    fontFamily = "Roboto"
                    bold = true
                }
            }

            // ...
        }
    }
}

Operator Overloading (docs)

Before knowing Kotlin I was pretty skeptical about operator overloading. In fact, I’m still not convinced it is a good coding approach.

But I can’t resist this cute and handy syntax. 😍

row {
    // initiates a cell with rotated text
    // using two operator overloads
    +"Am I rotated?" % 45
}

class RowDsl {
    // ...
    operator fun String.unaryPlus() = cell(this)
}

operator fun CellDsl.rem(angle: Int) = this.apply {
    rotation = angle
}

Lambda With Receiver (docs)

This feature allows to easily run custom methods in a certain instance context. Our init methods rely on this:

class RowDsl {

    private val cells = mutableListOf<CellDsl>()

    fun cell(value: String = "", init: CellDsl.() -> Unit = {}) =
        CellDsl(value).apply(init).also { cells += it }

    fun cell(value: Number, init: CellDsl.() -> Unit = {}) =
        CellDsl(value).apply(init).also { cells += it }

    // ...
}

Extension Functions (docs)

Thanks to them, we can define an extra method even for a 3rd party’s class.

fun Spreadsheet.openInBrowser() {
    Desktop.getDesktop().browse(URI.create(checkNotNull(spreadsheetUrl) {
        "Missing spreadsheetUrl!"
    }))
}

Infix Notation (docs)

Quite sophisticated syntax definition ability. Here used for the cell’s background and text alignment change.

cell("White on red and aligned to right") {
    font {
        color = WHITE
    }
} bg RED align RIGHT

infix fun CellDsl.bg(color: Color) = this.apply {
    backgroundColor = color
}

infix fun CellDsl.align(horizontalAlignment: HorizontalAlignmentDsl) = this.also {
    it.horizontalAlignment = horizontalAlignment
}

Context Control (docs)

During the composition of the spreadsheet tree, we call methods in multiple nested contexts. This can lead us to write something like this:

row {
    cell("1 - I'm OK") {
        cell("2 - Where am I? Next to the first cell or inside it?")
    }
}

IDE and compiler won’t argue with us. Even code will run without exception!

But I hope readers will agree that this syntax makes code more complicated. What’s more it brakes code simplicity and readability – the main advantages of using DSL.

But no worries! Kotlin has a cure for that! 💉

If we define an annotation for our DSL with @DslMarker applied to it:

@DslMarker
annotation class SheetsDslMarker

and we mark our context classes:

@SheetsDslMarker
class SheetDsl
// ...

@SheetsDslMarker
class CellDsl
// ...

Then IDE and the compiler will forbid us from calling a parent contexts method keeping the code easy to understand.

Conclusion

Designing a new DSL embedded in such a well-constructed language like Kotlin was very exciting and enjoyable for me. I hope this text will encourage you to experiment on your own.

I still don’t have enough experience to make a clear statement if DSLs in Kotlin increase code quality and are maintainable between modules, teams, or subsequent versions. Therefore, I leave judgment to my readers.

The format of this article required from me a few simplifications and a selective pick of topics. I hope readers find the below references as a comprehensive supplement to my text together with interesting examples of Kotlin DSLs found during resourcing.

The presented code can be found in this GitHub repo.

Reference

Docs

• Sheets API reference
• Kotlin’s docs about DSL

More readings

• Kotlin DSL: From Theory to Practice
• Building DSLs in Kotlin by baeldung
• DSL in Kotlin tutorial

DSL examples

• ExcelDSL
• Kotlin DSL for HTML
• Kotlin DSL for Spring Integration
• Village DSL