8

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 = "demo-983@praxis-practice-133423.iam.gserviceaccount.com";
    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

Gaurav Ashara
  • 462
  • 2
  • 6
  • 16
  • 1
    The sample request you've shown doesn't seem to include any quotes around the values - are you sure that's *really* the request? – Jon Skeet Jun 23 '16 at 08:33
  • @JonSkeet : We didn't include any quotes but Google Sheet API automatically include it to consider date as string. Above request is original request to append data mansion on screenshot. – Gaurav Ashara Jun 23 '16 at 11:41
  • So why did you remove the quotes from the request when you included it in the question? Basically, it's a lot easier to help you if you either provide the code that generates the request, or the actual request being made. Something that's "a bit like the request" but not actually the same is less helpful. – Jon Skeet Jun 23 '16 at 11:42
  • @JonSkeet : We didn't add this quote but api automatically take it and make value as string. Above request is original request generated by our code. We only modify sheetID nothing else. – Gaurav Ashara Jun 23 '16 at 12:13
  • Sorry, I don't understand - that clearly *isn't* the request that is being sent, because that would include quotes. What did you do to get the version you've copied and pasted here? – Jon Skeet Jun 23 '16 at 12:31
  • We want to append date in sheet's cell but Google Sheet API store value in string with quote(that is not included by us). – Gaurav Ashara Jun 23 '16 at 12:38
  • Well, I'd expect the *request* to have quotes because that's how the value is transferred. But I wouldn't expect it to end up in the final sheet. However, while you won't show the *actual* request or the code that produces it, it's very hard to help you. Ideally, provide a [mcve] so that we can reproduce the problem directly. – Jon Skeet Jun 23 '16 at 12:39
  • Here is sample code for append date value in sheet but Sheet API automatically convert to string value. https://www.dropbox.com/s/9v09bytis55sotl/DateIssueSample.java?dl=0 – Gaurav Ashara Jun 23 '16 at 12:48
  • 1
    No, please put the code *in the question* rather than linking to it on Dropbox. All of this is in the service of making this a good question for Stack Overflow. – Jon Skeet Jun 23 '16 at 12:49
  • Right, that's better - although for it to be *complete* (and to make it as easy as possible for others to reproduce the problem) it would be useful to have the import statements as well... the complete code you can just copy and paste into a new file. I'm trying to reproduce the problem now. – Jon Skeet Jun 23 '16 at 12:59
  • Ok Done @JonSkeet Let us know if you require more information. – Gaurav Ashara Jun 23 '16 at 12:59
  • Okay, I've reproduce the issue - now to fix it... – Jon Skeet Jun 23 '16 at 13:12
  • Can I check - is your ultimate goal to be able to specify the date as text, or are you just trying to get a date in there? Because I've got it working using `setNumberValue()`... – Jon Skeet Jun 23 '16 at 13:25
  • Our Ultimate go is store date in sheet cell. Thank you @JonSkeet – Gaurav Ashara Jun 23 '16 at 13:30
  • @JonSkeet please share your working sample. – Gaurav Ashara Jun 23 '16 at 13:37
  • Hi, i am confused about reference of sheetID that is being shown as integer while on excel sheet, sheet name is entered as string. can anyone clarify on it ? – Ankit Mar 15 '17 at 07:14

2 Answers2

10

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.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • We try this code but in sheet date stored as "1466690853849" .. String DateValue = "2015-07-13"; DateTime date = new DateTime(DateValue); Double val = Double.valueOf(date.getValue()); CellData cell = new CellData(); cell.setUserEnteredValue(new ExtendedValue().setNumberValue(val)); cell.setUserEnteredFormat(new CellFormat().setNumberFormat(new NumberFormat().setType("DATE"))); – Gaurav Ashara Jun 23 '16 at 14:09
  • @GauravAshara: Well yes, presumably `DateTime.getValue()` doesn't return "the number of days since December 31st 1899". I don't know which `DateTime` type that is, but that value looks like a "number of milliseconds since the Unix epoch" which isn't the same thing at all. – Jon Skeet Jun 23 '16 at 14:12
  • We uses google API DateTime import com.google.api.client.util.DateTime; – Gaurav Ashara Jun 23 '16 at 14:13
  • @GauravAshara: I'd suggest using `java.time` if you can; it'll make it easier to perform date/time arithmetic. But basically, we've now moved the problem from "How do I format a value for sheets" to "How do I get the number of days since December 31st 1899". I suggest you try to solve that, and post a new question if you can't figure it out. – Jon Skeet Jun 23 '16 at 14:15
  • 1
    I've updated the documentation @ https://developers.google.com/sheets/guides/concepts#datetime_serial_numbers to correctly say Dec 30 instead of Dec 31. Thanks @jonskeet for pointing out the problem! – Sam Berlin Jun 23 '16 at 16:35
  • how.the.hell can I convert normal date, like "20.10.2019" to that integer numberValue? – Starwave Jan 08 '20 at 17:54
  • @Starwave: Have you read https://developers.google.com/sheets/api/guides/concepts?nav=true#datetime_serial_numbers yet? – Jon Skeet Jan 08 '20 at 18:05
  • Yes, and I there is 0 mention of any built-in helper class that does that epoch-type conversion automatically. Letting humans do that integer construction manually is a giant flaw. I know I definately would oversee something by crafting it... – Starwave Jan 08 '20 at 18:24
  • This is the closest code-ready thingy that I found, but still figuring out the 1 day difference issue: https://stackoverflow.com/questions/38015854/java-epoch-date-for-google-spreadsheet – Starwave Jan 08 '20 at 18:26
5

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.

Sam Berlin
  • 3,603
  • 12
  • 23