Google Sheet API batch update issue iOS

Question

Am trying to do a batch update with google sheet API for iOS but am getting an error invalid value at 'data[0].values[0]' (type.googleapis.com/google.protobuf.ListValue),

here is my code

 NSString *baseUrl = @"https://sheets.googleapis.com/v4/spreadsheets/";
NSString *spreadsheetId = @"1tJd4toWFxmHAEOeONauRPcuH_rWJOESLQT7TvCIK0x0";

baseUrl= [baseUrl stringByAppendingString:spreadsheetId];
baseUrl = [baseUrl stringByAppendingString:@"/values:batchUpdate/"];



NSMutableDictionary * params=[[NSMutableDictionary alloc]initWithObjectsAndKeys:@"USER_ENTERED",@"valueInputOption", nil];

NSURL *postURL=[GTLUtilities URLWithString:baseUrl queryParameters:params];

NSLog(@"base url is %@", postURL);

GTLObject * body=[[GTLObject alloc]init];



NSMutableArray * titleArray=[[NSMutableArray alloc]initWithObjects:@"Customer ID",@"Customer Name",@"Latitude",@"Longitude" ,@"Last_Updated_At",nil];
NSMutableArray * wheelArray2=[[NSMutableArray alloc]initWithObjects:@"rt",@"SHJ",@"150.00",@"100.00",@"2:00:00", nil];

//[contentArray addObject:titleArray];



NSMutableDictionary * batchParams=[[NSMutableDictionary alloc]initWithObjectsAndKeys:@"USER_ENTERED",@"valueInputOption", nil];


NSMutableArray * batchParametersContentArray=[[NSMutableArray alloc]init];


NSMutableDictionary*  batchParametersTitlesDict=[[NSMutableDictionary alloc]initWithObjectsAndKeys:@"Sheet1!A1:E1",@"range",@"COLUMNS",@"majorDimension",titleArray,@"values", nil];


NSMutableDictionary*  batchParametersContentDict=[[NSMutableDictionary alloc]initWithObjectsAndKeys:@"Sheet1!A7:E7",@"range",@"ROWS",@"majorDimension",wheelArray2,@"values", nil];


[batchParametersContentArray addObject:batchParametersTitlesDict];

[batchParametersContentArray addObject:batchParametersContentDict];

[batchParams setObject:batchParametersContentArray forKey:@"data"];


NSLog(@"batch params are %@",batchParams);


body.JSON=batchParams;


[self.service fetchObjectByInsertingObject:body forURL:postURL completionHandler:^(GTLServiceTicket *ticket, id object, NSError *error) {
    if (error==nil) {
        NSLog(@"batch performed successfully");
    }
    else
    {
        NSLog(@"error is %@", error);
    }

}];

My Request parameters are

batch params are {
data =     (
            {
        majorDimension = COLUMNS;
        range = "Sheet1!A1:E1";
        values =             (
            "Customer ID",
            "Customer Name",
            Latitude,
            Longitude,
            "Last_Updated_At"
        );
    },
            {
        majorDimension = ROWS;
        range = "Sheet1!A7:E7";
        values =             (
            rt,
            SHJ,
            "150.00",
            "100.00",
            "2:00:00"
        );
    }
);
valueInputOption = "USER_ENTERED";
}

The error is

error is Error Domain=com.google.GTLJSONRPCErrorDomain Code=400 "(Invalid value at 'data[0].values[0]' (type.googleapis.com/google.protobuf.ListValue), "Customer ID"

Invalid value at 'data[0].values[1]' (type.googleapis.com/google.protobuf.ListValue), "Customer Name" Invalid value at 'data[0].values[2]' (type.googleapis.com/google.protobuf.ListValue), "Latitude" Invalid value at 'data[0].values[3]' (type.googleapis.com/google.protobuf.ListValue), "Longitude" Invalid value at 'data[0].values[4]' (type.googleapis.com/google.protobuf.ListValue), "Last_Updated_At" Invalid value at 'data[1].values[0]' (type.googleapis.com/google.protobuf.ListValue), "rt" Invalid value at 'data[1].values[1]' (type.googleapis.com/google.protobuf.ListValue), "SHJ" Invalid value at 'data[1].values[2]' (type.googleapis.com/google.protobuf.ListValue), "150.00" Invalid value at 'data[1].values[3]' (type.googleapis.com/google.protobuf.ListValue), "100.00" Invalid value at 'data[1].values[4]' (type.googleapis.com/google.protobuf.ListValue), "2:00:00")" UserInfo={error=Invalid value at 'data[0].values[0]' (type.googleapis.com/google.protobuf.ListValue), "Customer ID" Invalid value at 'data[0].values[1]' (type.googleapis.com/google.protobuf.ListValue), "Customer Name" Invalid value at 'data[0].values[2]' (type.googleapis.com/google.protobuf.ListValue), "Latitude" Invalid value at 'data[0].values[3]' (type.googleapis.com/google.protobuf.ListValue), "Longitude" Invalid value at 'data[0].values[4]' (type.googleapis.com/google.protobuf.ListValue), "Last_Updated_At" Invalid value at 'data[1].values[0]' (type.googleapis.com/google.protobuf.ListValue), "rt" Invalid value at 'data[1].values[1]' (type.googleapis.com/google.protobuf.ListValue), "SHJ" Invalid value at 'data[1].values[2]' (type.googleapis.com/google.protobuf.ListValue), "150.00" Invalid value at 'data[1].values[3]' (type.googleapis.com/google.protobuf.ListValue), "100.00" Invalid value at 'data[1].values[4]' (type.googleapis.com/google.protobuf.ListValue), "2:00:00", NSLocalizedFailureReason=(Invalid value at 'data[0].values[0]' (type.googleapis.com/google.protobuf.ListValue), "Customer ID" Invalid value at 'data[0].values[1]' (type.googleapis.com/google.protobuf.ListValue), "Customer Name" Invalid value at 'data[0].values[2]' (type.googleapis.com/google.protobuf.ListValue), "Latitude" Invalid value at 'data[0].values[3]' (type.googleapis.com/google.protobuf.ListValue), "Longitude" Invalid value at 'data[0].values[4]' (type.googleapis.com/google.protobuf.ListValue), "Last_Updated_At" Invalid value at 'data[1].values[0]' (type.googleapis.com/google.protobuf.ListValue), "rt" Invalid value at 'data[1].values[1]' (type.googleapis.com/google.protobuf.ListValue), "SHJ" Invalid value at 'data[1].values[2]' (type.googleapis.com/google.protobuf.ListValue), "150.00" Invalid value at 'data[1].values[3]' (type.googleapis.com/google.protobuf.ListValue), "100.00" Invalid value at 'data[1].values[4]' (type.googleapis.com/google.protobuf.ListValue), "2:00:00"), GTLStructuredError=GTLErrorObject 0x7f89ca465990: {code:400 message:"Invalid value at 'data[0].values[0]' (type.googleapis.com/google.protobuf.ListValue), "Customer ID" Invalid value at 'data[0].values[1]' (type.googleapis.com/google.protobuf.ListValue), "Customer Name" Invalid value at 'data[0].values[2]' (type.googleapis.com/google.protobuf.ListValue), "Latitude" Invalid value at 'data[0].values[3]' (type.googleapis.com/google.protobuf.ListValue), "Longitude" Invalid value at 'data[0].values[4]' (type.googleapis.com/google.protobuf.ListValue), "Last_Updated_At" Invalid value at 'data[1].values[0]' (type.googleapis.com/google.protobuf.ListValue), "rt" Invalid value at 'data[1].values[1]' (type.googleapis.com/google.protobuf.ListValue), "SHJ" Invalid value at 'data[1].values[2]' (type.googleapis.com/google.protobuf.ListValue), "150.00" Invalid value at 'data[1].values[3]' (type.googleapis.com/google.protobuf.ListValue), "100.00" Invalid value at 'data[1].values[4]' (type.googleapis.com/google.protobuf.ListValue), "2:00:00"" errors?:[1] status?:"INVALID_ARGUMENT"}}

score 22 · Answer 1 · answered Aug 11 '16 at 18:04

22

Values are intended to be a 2d array. They should be in the form of [[ 'a', 'b', 'c'], [ 1, 2, 3 ]]

You're supplying the values as a 1d array ['a', 'b', 'c'], and the error message is complaining the 'a' can't fit into a ListValue since it's not an array. The solution is to use a 2d array, although I can't provide an example here of exactly what you should do since I'm not sure how you intend your input to map to the spreadsheet.

answered Aug 11 '16 at 18:04

Sam Berlin

3,603
12
23

I just created a 2D array, { data = ( ( "Customer ID", "Customer Name", Latitude, Longitude, "Last_Updated_At" ), ( rt, SHJ, "150.00", "100.00", "2:00:00" ) ); valueInputOption = "USER_ENTERED"; } Invalid value at 'data[0]' (type.googleapis.com/google.apps.sheets.v4.ValueRange), "Customer ID" – Syed Ismail Ahamed Aug 11 '16 at 18:37
The data field isn't a 2d array, each values field is. The data field was correct as you had it before. – Sam Berlin Aug 11 '16 at 23:49
{ data = ({ majorDimension = ROWS; range = "Sheet1!A7:E7"; values = ( ( "Customer ID", "Customer Name", Latitude, Longitude, "Last_Updated_At" ), ( rt, SHJ, "150.00", "100.00", "2:00:00" ) ); });valueInputOption = "USER_ENTERED"; } – Syed Ismail Ahamed Aug 12 '16 at 05:01
1

it failed with error Error Domain=com.google.GTLJSONRPCErrorDomain Code=400 "(Invalid data[0]: Requested writing within range [Sheet1!A7:E7], but tried writing to row [8]) – Syed Ismail Ahamed Aug 13 '16 at 08:47
That's your new problem then. You asked to only write in row 7, but included data that would have extended the write to row 8. You can either change your requested range to be larger, or remove data so it only writes one row. – Sam Berlin Aug 13 '16 at 11:36
Will just add, that if you need to add only one row, so you will end up with an array in an array like this [['a', 'b', 'c']]. (I needed this array visualized to understand what Sam answered). – bets Jul 13 '22 at 17:33

score 0 · Answer 2 · answered May 08 '22 at 18:06

I don't have enough points to comment on Sam Berlin's post but I have been struggling to work out why I can't add a row to a google sheet and it turns out that I was using a string rather than a 2d array as required in: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#ValueRange (I was starting to wonder whether it was a google authentication thing or some other area of my involved system using Ionic/Angular/Google sheets https://github.com/doughazell/ionic-camera-swipe )

score -1 · Answer 3 · answered Aug 12 '16 at 18:48

The error indicates that a request parameter has an invalid value and the error response provide the information as to which value was invalid. Do not retry without fixing the problem. You need to provide a valid value for the parameter specified in the error response. You need to make changes to the API query in order for it to work. Your Customer Name, Latitude, Longitude value was invalid. Check your error log.

Here's a Sheets API BatchUpdate blog encountered 400 error response: https://github.com/google/google-api-nodejs-client/issues/588

Google Sheet API batch update issue iOS

3 Answers3

Linked