Updating a worksheetsheet within a Google Spreadsheet (ruby api)

user_10227093 0
Viewed 272

I have figured out a way to add a new worksheet to a an existing spreadsheet BUT I can't seem to figure out how to format the added worksheet.

For example, I can color the header row of the first worksheet (that is the default worksheet) in a spreadsheet using the method below:

  def color_header_row(file)
    spreadsheet_id = file.id
    requests = {
      requests: [
        {
          repeat_cell: {
            range: {
              sheet_id: 0,
              start_row_index: 0,
              end_row_index: 1
            },
            cell: {
              user_entered_format: {
                background_color: { red: 0.0, green: 0.4, blue: 0.0 },
                horizontal_alignment: "CENTER",
                text_format: {
                  foreground_color: {
                    red: 1.0,
                    green: 1.0,
                    blue: 1.0
                  },
                  font_size: 12,
                  bold: true
                }
              }
            },
            fields: 'userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)'
          },
        }
      ]
    }
    sheet_service.batch_update_spreadsheet(spreadsheet_id, requests, {})
  end

Notice that the default worksheet sheet_id is 0 thus my assumption (given how GridRange is defined in the documentation) was that setting sheet_id to 1 will result in a reference to the newly added worksheet (in essence worksheet at position 1). Yet when sheet_id is set to 1, the error Invalid request(Google::Apis::ClientError) is returned.

Any idea on how to format the header row of a non-default (that is a worksheet that isn't the in the first position in a spreadsheet) worksheet?

google-api-ruby-client google-apis-explorer google-sheets-api google-sheets ruby
1 Answers

I believe your goal and your current situation as follows.

  • You want to use repeat_cell for the worksheet in Google Spreadsheet except for the sheet ID 0.
  • You want to achieve this using googleapis for ruby.
  • You have already been able to get and put values for Spreadsheet using Sheets API.

In this case, how about retrieving the sheet ID using the sheet name with the method of spreadsheets.get? In the Spreadsheet, the same sheet name cannot be used. So, in this case, I thought that this direction might be useful for your situation. When this is reflected to your script, it becomes as follows.

Modified script:

In this script, the sheet ID is retrieved using the sheet name of "Sheet2" and the retrieved sheet ID is used for repeat_cell request.

sheet_name = 'Sheet2' # Please set the sheet name.

spreadsheet_id = file.id
response = sheet_service.get_spreadsheet(spreadsheet_id, ranges: [sheet_name], fields: 'sheets(properties)')
sheet_id = response.sheets[0].properties.sheet_id

requests = {
  requests: [
    {
      repeat_cell: {
        range: {
          sheet_id: sheet_id,
          start_row_index: 0,
          end_row_index: 1
        },
        cell: {
          user_entered_format: {
            background_color: { red: 0.0, green: 0.4, blue: 0.0 },
            horizontal_alignment: "CENTER",
            text_format: {
              foreground_color: {
                red: 1.0,
                green: 1.0,
                blue: 1.0
              },
              font_size: 12,
              bold: true
            }
          }
        },
        fields: 'userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)'
      },
    }
  ]
}
sheet_service.batch_update_spreadsheet(spreadsheet_id, requests, {})

Note:

  • If you want to retrieve the sheet ID using the sheet index (For example, the 1st and 2nd sheets are 0 and 1, respectively.), you can also use the following script.

      sheet_index = 1
  response = sheet_service.get_spreadsheet(spreadsheet_id, fields: 'sheets(properties)')
  sheet_id = response.sheets[sheet_index].properties.sheet_id

Reference:

user_71086530
Related