Google Sheet API V4(Java) append Date in cells

Question

I am tried to add date in cells but sheet automatically store value in string with single quote ('). For Store value in date , We also try to add userEnteredFormat but it didn't work for us.

Below are append request.

{
requests = [{
    appendCells = {
        fields = userEnteredValue,
        userEnteredFormat.numberFormat,
        rows = [{
            values = [{
                userEnteredValue = {
                    numberValue = 10.0
                }
            }, {
                userEnteredValue = {
                    stringValue = Sample String
                }
            }, {
                userEnteredFormat = {
                    numberFormat = {
                        type = DATE
                    }
                },
                userEnteredValue = {
                    stringValue = 2015 - 07 - 13
                }
            }, {
                userEnteredValue = {
                    boolValue = true
                }
            }, {
                userEnteredFormat = {
                    numberFormat = {
                        type = DATE
                    }
                },
                userEnteredValue = {
                    stringValue = 2015 - 07 - 13
                }
            }]
        }],
        sheetId = abc
    }
}]}

Sample Code to append single date cells on sheet

package org.pentaho.googlesheets.api;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.pentaho.di.i18n.BaseMessages;
import org.pentaho.pdi.steps.googlesheets.GoogleSheetsOutputStepMeta;

import com.google.api.client.auth.oauth2.Credential;
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.gson.GsonFactory;
import com.google.api.services.drive.DriveScopes;
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.ExtendedValue;
import com.google.api.services.sheets.v4.model.NumberFormat;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.RowData;

public class DateIssueSample {

    static String APPLICATION_NAME ;
    static JsonFactory JSON_FACTORY;
    static HttpTransport HTTP_TRANSPORT;
    static List<String> SPREADSHEET_SCOPES ;
    static List<String> DRIVE_SCOPES ;
    static Sheets service;

    static String email = "[email protected]";
    static String pkey ="E:\\P12Key\\My Project-834a8d37d247.p12";

    public static Credential authorize(List<String> SCOPES ) throws Exception {
        GoogleCredential credential = new GoogleCredential.Builder()
        .setTransport(HTTP_TRANSPORT)
        .setJsonFactory(JSON_FACTORY)
        .setServiceAccountId(email)
        .setServiceAccountScopes(SCOPES)
        .setServiceAccountPrivateKeyFromP12File(new java.io.File(pkey))
        .build();
        credential.refreshToken();

        return credential;

    }
    public static  Sheets getSheetsService() throws Exception {
        Credential credential = authorize(SPREADSHEET_SCOPES);
        return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
                .setApplicationName(APPLICATION_NAME)
                .build();
    }
    public static void main(String[] args) throws Exception {
        APPLICATION_NAME = "PDI";
        JSON_FACTORY =new GsonFactory();
        SPREADSHEET_SCOPES =Arrays.asList(SheetsScopes.SPREADSHEETS);
        DRIVE_SCOPES=Arrays.asList(DriveScopes.DRIVE_METADATA_READONLY);
        HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();

        service = getSheetsService();

        String spreadSheetID= "abc";
        Integer sheetID = 123;
        String DateValue = "2015-07-13";

        List<RowData> rowData = new ArrayList<RowData>();
        List<CellData> cellData = new ArrayList<CellData>();

        CellData cell = new CellData();
        cell.setUserEnteredValue(new ExtendedValue().setStringValue(DateValue));
        cell.setUserEnteredFormat(new CellFormat().setNumberFormat(new NumberFormat().setType("DATE")));

        cellData.add(cell);
        rowData.add(new RowData().setValues(cellData));

        BatchUpdateSpreadsheetRequest batchRequests = new BatchUpdateSpreadsheetRequest();
        BatchUpdateSpreadsheetResponse response;
        List<Request> requests = new ArrayList<Request>();      

        AppendCellsRequest appendCellReq = new AppendCellsRequest();
        appendCellReq.setSheetId( sheetID);
        appendCellReq.setRows( rowData );           
        appendCellReq.setFields("userEnteredValue,userEnteredFormat.numberFormat");


        requests = new ArrayList<Request>();
        requests.add( new Request().setAppendCells(appendCellReq));
        batchRequests = new BatchUpdateSpreadsheetRequest();
        batchRequests.setRequests( requests );      


        response=  service.spreadsheets().batchUpdate(spreadSheetID, batchRequests).execute();
        System.out.println("Request \n\n");
        System.out.println(batchRequests.toPrettyString());
        System.out.println("\n\nResponse \n\n");
        System.out.println(response.toPrettyString());
    }

}

SpreadSheet , Sample Code for Single Date value append

Answer 1

To provide an example of what Sam's answer means, if you just want to create a date value using AppendCellsRequest, you can create the cell like this:

CellData cell = new CellData();
cell.setUserEnteredValue(new ExtendedValue().setNumberValue(42198.0));
cell.setUserEnteredFormat(
    new CellFormat().setNumberFormat(new NumberFormat().setType("DATE")));    

Here 42198 is the number of days between December 30th 1899 and July 13th 2015.

Answer 2

See the intro guide explanation on how the API works with datetimes.

Dates in Sheets are numbers, not strings. (This lets you, for example, do arithmetic over them.)

If using the 'values' collection, there's hooks for translating from string to date and vice versa (using different ValueInputOptions or ValueRenderOptions.

Unfortunately, there's no Append method yet in the values collection. So to easily append cells after existing data, you need to use spreadsheets.batchUpdate, and that's just the raw spreadsheet DOM. So for now, you'll need to input the dates as the serial numbers (with date formatting), as described in the first link.

The reason the strings are being added with a single quote is because you're telling the API you want to add a string, so a quote is being prefixed to prevent Sheets from accidentally parsing the value into a date.