Updating Spreadsheets

Aside from the data contained in its cells, a spreadsheet includes many other types of data, such as:

  • Cell formats
  • Cell borders
  • Named ranges
  • Protected ranges
  • Conditional formatting

These are just some of the many kinds of data that control the appearance and operation of a spreadsheet. The batchUpdate method lets you update any of these spreadsheet details. Changes are grouped together in a batch so that if one request fails, none of the other (potentially dependent) changes is written.

Categories of operation

The particular operations supported by batchUpdate can be grouped into the following broad categories:

Category Description
Add (and Duplicate)Add new objects (sometimes based on old ones, as in the Duplicate requests).
Update (and Set)Update certain properties of an object, usually leaving the old properties alone (whereas Set requests will overwrite the prior data).
DeleteRemove objects.

These categories are used in the next section to describe the behavior of specific operations.

Batch update operations

The batchUpdate method works by taking one or more Request objects, each one specifying a single kind of request to perform. There are many different kinds of requests. Here's a breakdown on the types of requests, grouped into different categories.

Object ADD / DUPLICATE UPDATE / SET DELETE
Spreadsheet Properties UpdateSpreadsheetPropertiesRequest
Sheets AddSheetRequest
DuplicateSheetRequest
UpdateSheetPropertiesRequest DeleteSheetRequest
Dimensions (including their properties) InsertDimensionRequest
AppendDimensionRequest
UpdateDimensionPropertiesRequest
MoveDimensionRequest
AutoResizeDimensionsRequest
DeleteDimensionRequest
Cells (including values, formats, data validation, etc.) RepeatCellRequest
UpdateCellsRequest
AppendCellsRequest
Named Ranges AddNamedRangeRequest UpdateNamedRangeRequest DeleteNamedRangeRequest
Borders UpdateBordersRequest
Filters (filter views & the basic filter) AddFilterViewRequest
DuplicateFilterViewRequest
UpdateFilterViewRequest
SetBasicFilterRequest
ClearBasicFilterRequest
Data Validation SetDataValidationRequest
Conditional Format Rules AddConditionalFormatRuleRequest UpdateConditionalFormatRuleRequest DeleteConditionalFormatRuleRequest
Protected Ranges AddProtectedRangeRequest UpdateProtectedRangeRequest DeleteProtectedRangeRequest
Embedded Objects (including charts) AddChartRequest UpdateChartSpecRequest
UpdateEmbeddedObjectPositionRequest
DeleteEmbeddedObjectRequest
Merges MergeCellsRequest UnmergeCellsRequest

There are also some additional requests that mimic user actions for manipulating data:

Field masks

Many of the "Update" requests require field masks. These are a comma-delimited list of fields that you want to update. The mask is required to make sure only fields you want to edit are updated. You can use a "*" as short-hand for updating every field (which means a field may revert to its default state if you don't specify a value for it in the request).

For example, to update just the Title of a spreadsheet, this would be the request:

Request:

POST .../v4/spreadsheets/spreadsheetId:batchUpdate

Request body:

{
  "requests": [{
      "updateSpreadsheetProperties": {
          "properties": {"title": "My New Title"},
          "fields": "title"
        }
    }]
}

Responses

When updating a spreadsheet, some kinds of requests may return responses. These are returned in an array, with each response occupying the same index as the corresponding request. Some requests do not have responses. For those requests, the response will be empty.

Typically the "Add" requests will have responses, so that you know information (such as the ID) of the newly added object. See Response for the list of supported responses.

Example

Protocol

The example below performs the following actions:

  1. Change the name of sheet ID '0' (the default first sheet on every spreadsheet).
  2. Insert the values 1, 2, 3 into the first row of the spreadsheet with a different background color in each.
  3. Write "=A1+1" into A2 and fill the formula across A2:C5 (so B2 is "=B1+1", C2 is "=C1+1", A3 is "=A2+1", etc.)
  4. Copy the format from A1:C1 and paste it into A2:C5, so the data in each column has the same background.

Request:

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate

Request body:

{
  "requests": [
    {
      "updateSheetProperties": {
        "properties": {"sheetId": 0, "title": "New Sheet Name"},
        "fields": "title"
      }
    },
    {
      "updateCells": {
        "start": {
          "sheetId": 0,
          "rowIndex": 0,
          "columnIndex": 0
        },
        "rows": [
          {
            "values": [
              {
                "userEnteredValue": {"numberValue": 1},
                "userEnteredFormat": {"backgroundColor": {"red": 1}}
              }, {
                "userEnteredValue": {"numberValue": 2},
                "userEnteredFormat": {"backgroundColor": {"blue": 1}}
              }, {
                "userEnteredValue": {"numberValue": 3},
                "userEnteredFormat": {"backgroundColor": {"green": 1}}
              }
            ]
          }
        ],
        "fields": "userEnteredValue,userEnteredFormat.backgroundColor"
      }
    },
    {
      "repeatCell": {
        "range": {
          "sheetId": 0,
          "startRowIndex": 1,
          "startColumnIndex": 0,
          "endRowIndex": 6,
          "endColumnIndex": 3
        },
        "cell": {"userEnteredValue": {"formulaValue": "=A1 + 1"}},
        "fields": "userEnteredValue"
      }
    },
    {
      "copyPaste": {
        "source": {
          "sheetId": 0,
          "startRowIndex": 0,
          "startColumnIndex": 0,
          "endRowIndex": 1,
          "endColumnIndex": 3
        },
        "destination":  {
          "sheetId": 0,
          "startRowIndex": 1,
          "startColumnIndex": 0,
          "endRowIndex": 6,
          "endColumnIndex": 3
        },
        "pasteType": "PASTE_FORMAT"
      }
    }
  ]
}

Java

The example below performs the following actions:

  1. Change the name of sheet ID '0' (the default first sheet on every spreadsheet).
  2. Insert the values 1, 2, 3 into the first row of the spreadsheet with a different background color in each.
  3. Write "=A1+1" into A2 and fill the formula across A2:C5 (so B2 is "=B1+1", C2 is "=C1+1", A3 is "=A2+1", etc.)
  4. Copy the format from A1:C1 and paste it into A2:C5, so the data in each column has the same background.

List<Request> requests = new ArrayList<>();
// Change the spreadsheet's title.
requests.add(new Request()
        .setUpdateSpreadsheetProperties(new UpdateSpreadsheetPropertiesRequest()
                .setProperties(new SpreadsheetProperties()
                        .setTitle(title))
                .setFields("title")));
// Find and replace text.
requests.add(new Request()
        .setFindReplace(new FindReplaceRequest()
                .setFind(find)
                .setReplacement(replacement)
                .setAllSheets(true)));
// Add additional requests (operations) ...

BatchUpdateSpreadsheetRequest body =
        new BatchUpdateSpreadsheetRequest().setRequests(requests);
BatchUpdateSpreadsheetResponse response =
        service.spreadsheets().batchUpdate(spreadsheetId, body).execute();
FindReplaceResponse findReplaceResponse = response.getReplies().get(1).getFindReplace();
System.out.printf("%d replacements made.", findReplaceResponse.getOccurrencesChanged());

Node.js

The example below performs the following actions:

  1. Change the name of sheet ID '0' (the default first sheet on every spreadsheet).
  2. Insert the values 1, 2, 3 into the first row of the spreadsheet with a different background color in each.
  3. Write "=A1+1" into A2 and fill the formula across A2:C5 (so B2 is "=B1+1", C2 is "=C1+1", A3 is "=A2+1", etc.)
  4. Copy the format from A1:C1 and paste it into A2:C5, so the data in each column has the same background.

var spreadsheetId = '123abc';
var requests = [];
// Change the name of sheet ID '0' (the default first sheet on every
// spreadsheet)
requests.push({
  updateSheetProperties: {
    properties: {sheetId: 0, title: 'New Sheet Name'},
    fields: 'title'
  }
});
// Insert the values 1, 2, 3 into the first row of the spreadsheet with a
// different background color in each.
requests.push({
  updateCells: {
    start: {sheetId: 0, rowIndex: 0, columnIndex: 0},
    rows: [{
      values: [{
        userEnteredValue: {numberValue: 1},
        userEnteredFormat: {backgroundColor: {red: 1}}
      }, {
        userEnteredValue: {numberValue: 2},
        userEnteredFormat: {backgroundColor: {blue: 1}}
      }, {
        userEnteredValue: {numberValue: 3},
        userEnteredFormat: {backgroundColor: {green: 1}}
      }]
    }],
    fields: 'userEnteredValue,userEnteredFormat.backgroundColor'
  }
});
// Write "=A1+1" into A2 and fill the formula across A2:C5 (so B2 is
// "=B1+1", C2 is "=C1+1", A3 is "=A2+1", etc..)
requests.push({
  repeatCell: {
    range: {
      sheetId: 0,
      startRowIndex: 1,
      endRowIndex: 6,
      startColumnIndex: 0,
      endColumnIndex: 3
    },
    cell: {userEnteredValue: {formulaValue: '=A1 + 1'}},
    fields: 'userEnteredValue'
  }
});
// Copy the format from A1:C1 and paste it into A2:C5, so the data in
// each column has the same background.
requests.push({
  copyPaste: {
    source: {
      sheetId: 0,
      startRowIndex: 0,
      endRowIndex: 1,
      startColumnIndex: 0,
      endColumnIndex: 3
    },
    destination: {
      sheetId: 0,
      startRowIndex: 1,
      endRowIndex: 6,
      startColumnIndex: 0,
      endColumnIndex: 3
    },
    pasteType: 'PASTE_FORMAT'
  }
});

var batchUpdateRequest = {requests: requests}

service.spreadsheets.batchUpdate({
  spreadsheetId: spreadsheetId,
  resource: batchUpdateRequest
}, function(err, response) {
  if(err) {
    // Handle error
    console.log(err);
  }
});

PHP

The example below updates the title of the spreadsheet and replaces some text.

$requests = array();
// Change the spreadsheet's title
$requests[] = new Google_Service_Sheets_Request(array(
  'updateSpreadsheetProperties' => array(
    'properties' => array(
      'title' => $title
    ),
    'fields' => 'title'
  )
));
// Find and replace text
$requests[] = new Google_Service_Sheets_Request(array(
  'findReplace' => array(
    'find' => $find,
    'replacement' => $replacement,
    'allSheets' => TRUE
  )
));
// Add additional requests (operations) ...

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
  'requests' => $requests
));

$response = $service->spreadsheets->batchUpdate($spreadsheetId,
    $batchUpdateRequest);
$findReplaceResponse = $response->getReplies()[1]->getFindReplace();
printf("%s replacements made.\n",
    $findReplaceResponse->getOccurrencesChanged());

Python

The example below updates the title of the spreadsheet and replaces some text.

requests = []
# Change the spreadsheet's title
requests.append({
    'updateSpreadsheetProperties': {
        'properties': {
            'title': title
        },
        'fields': 'title'
    }
})
# Find and replace text
requests.append({
    'findReplace': {
        'find': find,
        'replacement': replacement,
        'allSheets': True
    }
})
# Add additional requests (operations) ...

body = {
    'requests': requests
}
response = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id,
                                               body=body).execute()
find_replace_response = response.get('replies')[1].get('findReplace')
print('{0} replacements made.'.format(
    find_replace_response.get('occurrencesChanged')))

Ruby

The example below performs the following actions:

  1. Change the name of sheet ID '0' (the default first sheet on every spreadsheet).
  2. Insert the values 1, 2, 3 into the first row of the spreadsheet with a different background color in each.
  3. Write "=A1+1" into A2 and fill the formula across A2:C5 (so B2 is "=B1+1", C2 is "=C1+1", A3 is "=A2+1", etc.)
  4. Copy the format from A1:C1 and paste it into A2:C5, so the data in each column has the same background.

spreadsheet_id = '123abc'
requests = []
# Change the name of sheet ID '0' (the default first sheet on every
# spreadsheet)
requests.push({
  update_sheet_properties: {
    properties: {sheet_id: 0, title: 'New Sheet Name'},
    fields: 'title'
  }
})
# Insert the values 1, 2, 3 into the first row of the spreadsheet with a
# different background color in each.
requests.push({
  update_cells: {
    start: {sheet_id: 0, row_index: 0, column_index: 0},
    rows: [
      {
        values: [
          {
            user_entered_value: {number_value: 1},
            user_entered_format: {background_color: {red: 1}}
          }, {
            user_entered_value: {number_value: 2},
            user_entered_format: {background_color: {blue: 1}}
          }, {
            user_entered_value: {number_value: 3},
            user_entered_format: {background_color: {green: 1}}
          }
        ]
      }
    ],
    fields: 'userEnteredValue,userEnteredFormat.backgroundColor'
  }
})
# Write "=A1+1" into A2 and fill the formula across A2:C5 (so B2 is
# "=B1+1", C2 is "=C1+1", A3 is "=A2+1", etc..)
requests.push({
  repeat_cell: {
    range: {
      sheet_id: 0,
      start_row_index: 1,
      end_row_index: 6,
      start_column_index: 0,
      end_column_index: 3
    },
    cell: {user_entered_value: {formula_value: '=A1 + 1'}},
    fields: 'userEnteredValue'
  }
})
# Copy the format from A1:C1 and paste it into A2:C5, so the data in
# each column has the same background.
requests.push({
  copy_paste: {
    source: {
      sheet_id: 0,
      start_row_index: 0,
      end_row_index: 1,
      start_column_index: 0,
      end_column_index: 3
    },
    destination: {
      sheet_id: 0,
      start_row_index: 1,
      end_row_index: 6,
      start_column_index: 0,
      end_column_index: 3
    },
    paste_type: 'PASTE_FORMAT'
  }
})

batch_update_request = {requests: requests}
service.batch_update_spreadsheet(spreadsheet_id, batch_update_request, {})

There are a number of different ways to achieve the same result—the above request is just one way of doing it.

Send feedback about...