SheetAPI - NDDCoder

package app

import com.google.api.client.auth.oauth2.Credential
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport
import com.google.api.client.http.HttpTransport
import com.google.api.client.json.JsonFactory
import com.google.api.client.json.jackson2.JacksonFactory
import com.google.api.client.util.store.FileDataStoreFactory
import com.google.api.services.sheets.v4.Sheets
import com.google.api.services.sheets.v4.SheetsScopes
import com.google.api.services.sheets.v4.model.AppendCellsRequest
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse
import com.google.api.services.sheets.v4.model.CellData
import com.google.api.services.sheets.v4.model.CellFormat
import com.google.api.services.sheets.v4.model.Color
import com.google.api.services.sheets.v4.model.ExtendedValue
import com.google.api.services.sheets.v4.model.GridCoordinate
import com.google.api.services.sheets.v4.model.GridRange
import com.google.api.services.sheets.v4.model.RepeatCellRequest
import com.google.api.services.sheets.v4.model.Request
import com.google.api.services.sheets.v4.model.RowData
import com.google.api.services.sheets.v4.model.Sheet
import com.google.api.services.sheets.v4.model.SheetProperties
import com.google.api.services.sheets.v4.model.Spreadsheet
import com.google.api.services.sheets.v4.model.SpreadsheetProperties
import com.google.api.services.sheets.v4.model.TextFormat
import com.google.api.services.sheets.v4.model.UpdateCellsRequest
import com.google.api.services.sheets.v4.model.UpdateSheetPropertiesRequest
import com.google.api.services.sheets.v4.model.UpdateValuesResponse
import com.google.api.services.sheets.v4.model.ValueRange
import groovy.json.JsonOutput
import groovy.time.TimeCategory

import java.security.GeneralSecurityException

import static groovy.json.JsonOutput.prettyPrint
import static groovy.json.JsonOutput.toJson

class SheetAPI {

private static final java.io.File DATA_STORE_DIR = new File("SheetCredential")
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance()
    private static HttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport()

static void main(String[] args) throws IOException, GeneralSecurityException {
        createNewSpreadsheet(" - " + new Date().format("dd-MM-YYYY"))
    }

static getInfoSpreadSheet(String spreadsheetId) {

boolean includeGridData = false;

Sheets sheetsService = createSheetsService();
        Sheets.Spreadsheets.Get request = sheetsService.spreadsheets().get(spreadsheetId);
        request.setIncludeGridData(includeGridData);

return request.execute();
    }

static updateSheetBackground(String spreadsheetId, int sheetId, String topLeft, String bottomRight, int R, int G, int B) {

CellData setUserEnteredValue = new CellData()
                .setUserEnteredValue(new ExtendedValue())

CellFormat myFormat = new CellFormat();
        myFormat.setBackgroundColor(new Color().setGreen(1)); // red background
        myFormat.setTextFormat(new TextFormat().setFontSize(16)); // 16pt font

setUserEnteredValue.setUserEnteredFormat(myFormat);

def updateCellRequest = new RepeatCellRequest();

def range = new GridRange()
        range.setSheetId(sheetId)
        range.setStartColumnIndex(2)
        range.setEndColumnIndex(5)
        range.setStartRowIndex(0)
        range.setEndRowIndex(2)

updateCellRequest.setRange(range)
        updateCellRequest.setCell(setUserEnteredValue)

List<Request> requests = new ArrayList<>();
        requests.add(new Request().setRepeatCell(updateCellRequest.setFields("*")))

Sheets sheetsService = createSheetsService()
        BatchUpdateSpreadsheetRequest body =
                new BatchUpdateSpreadsheetRequest().setRequests(requests);
        BatchUpdateSpreadsheetResponse response =
                sheetsService.spreadsheets().batchUpdate(spreadsheetId, body).execute();

}

static updateSheetValue(String spreadsheetId, String sheetName, int scol, int ecol, int srow, int erow, List<List<String> > value) {
        String range = sheetName + "!" + convertIndexToStringRange(scol, srow) + ":" + convertIndexToStringRange(ecol, erow)

ValueRange requestBody = new ValueRange()

requestBody.setMajorDimension("ROWS")
        requestBody.setValues(value)

Sheets sheetsService = createSheetsService()
        Sheets.Spreadsheets.Values.Update request =
                sheetsService.spreadsheets().values().update(spreadsheetId, range, requestBody)
        request.setValueInputOption("USER_ENTERED")

UpdateValuesResponse response = request.execute()

return response
    }

static createSpreadsheet(String fileName, String sheetName) {
        Spreadsheet requestBody = new Spreadsheet()

requestBody.setProperties(new SpreadsheetProperties().setTitle(fileName))
        requestBody.setSheets([
                new Sheet().setProperties(new SheetProperties().setTitle(sheetName))
        ])

Sheets sheetsService = createSheetsService()
        Sheets.Spreadsheets.Create request = sheetsService.spreadsheets().create(requestBody)

Spreadsheet response = request.execute()

return response
    }

static Sheets createSheetsService() throws IOException, GeneralSecurityException {
        HttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport()
        JsonFactory jsonFactory = JacksonFactory.getDefaultInstance()

Credential credential = authorize()

return new Sheets.Builder(httpTransport, jsonFactory, credential)
                .setApplicationName("Google-SheetsSample/0.1")
                .build()
    }

static Credential authorize() throws IOException {
        InputStream inputStream = this.getClass().getResourceAsStream("/client_secret_test.json")
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(inputStream))

GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
                HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, [SheetsScopes.SPREADSHEETS])
                .setDataStoreFactory(new FileDataStoreFactory(DATA_STORE_DIR))
                .setAccessType("offline")
                .build()
        Credential credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user")
        return credential
    }

static convertIndexToStringRange(int columnNumber, row)
    {
        // To store result (Excel column name)
        StringBuilder columnName = new StringBuilder();

while (columnNumber > 0)
        {
            // Find remainder
            int rem = columnNumber % 26;

// If remainder is 0, then a
            // 'Z' must be there in output
            if (rem == 0)
            {
                columnName.append("Z");
                columnNumber = (int) ((columnNumber / 26) - 1);
            }
            else // If remainder is non-zero
            {
                columnName.append(Character.toChars(rem - 1 + 65));
                columnNumber = (int) (columnNumber / 26);
            }
        }

// Reverse the string and print result
        return columnName.reverse().toString() + row;
    }

static log(obj) {
        println prettyPrint(toJson(obj))

}
}