CODEWITHSUNDEEP
google-sheets-api
user10713673
user10713673

Reputation:

How to add a hyperlink using Google Sheets API?

I'm trying to write a python script to add hyperlinks to a google sheet. I'm using the google api for this. From searching, I've gathered that I need pass the rest api a "=HYPERLINK()" type of message.

From documentation: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ExtendedValue.FIELDS.formula_value

{
  // Union field value can be only one of the following:
  "numberValue": number,
  "stringValue": string,
  "boolValue": boolean,
  "formulaValue": string,
  "errorValue": {
    object(ErrorValue)
  }
  // End of list of possible types for union field value.
}

It looks like I should be using the 'formulaValue' property.

Edit: I've attempted to use the UpdateCells request

Edit: Solution below.

Upvotes: 3

Views: 4217

Answers (2)

Максим Нагуслаев
Максим Нагуслаев

Reputation: 1

links not worked!!! But add like note)

def addHyperlink(self, uri_1, summa_, sheetId, rowIndex, colIndex):
    requests = []
    requests.append({
        "updateCells": {
            "rows": [{
                "values": [{
                    'userEnteredValue': {'numberValue': float(summa_)}, #
                    'effectiveValue': {'numberValue': float(summa_)},
                    'formattedValue': "р."+summa_,
                    'userEnteredFormat': {
                        'numberFormat': {'type': 'NUMBER', 'pattern': '[$р.-419]#,##0.00'},
                        'backgroundColor': {'red': 1, 'green': 1, 'blue': 0.6}, 'borders': {
                            'top': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}},
                            'bottom': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}},
                            'left': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}},
                            'right': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}}},
                        'horizontalAlignment': 'RIGHT', 'verticalAlignment': 'BOTTOM',
                        'textFormat': {
                            'foregroundColor': {'red': 0.06666667, 'green': 0.33333334, 'blue': 0.8},
                            'fontFamily': 'Arial', 'underline': True,
                            'foregroundColorStyle': {'rgbColor': {'red': 0.06666667, 'green': 0.33333334, 'blue': 0.8}},
                            'link': {'uri': uri_1}
                        },
                        'hyperlinkDisplayType': 'LINKED','backgroundColorStyle': {'rgbColor': {'red': 1, 'green': 1, 'blue': 0.6}}
                    }
                    ,                            'hyperlink': uri_1 , 'note': uri_1,
                }
                ]
            }], "fields": '*', "start": {
                "sheetId": sheetId, "rowIndex": rowIndex, "columnIndex": colIndex}}})
    body = {
        "requests": requests}
    request = self.service.spreadsheets().batchUpdate(spreadsheetId=self.spreadsheetId, body=body)
    return request.execute()

Upvotes: -1

user10713673
user10713673

Reputation:

I figured it out:

def addHyperlink(self, hyperlink, text, sheetId, rowIndex, colIndex):
    requests = []
    requests.append({
        "updateCells": {
            "rows": [
                {
                    "values": [{
                        "userEnteredValue": {
                            "formulaValue":"=HYPERLINK({},{})".format(hyperlink, text) 
                        }
                    }]
                }
            ],
            "fields": "userEnteredValue",
            "start": {
                "sheetId": sheetId,
                "rowIndex": rowIndex,
                "columnIndex": colIndex
            }
        }})
    body = {
        "requests": requests
    }
    request = self.service.spreadsheets().batchUpdate(spreadsheetId=self.spreadsheetId, body=body)
    return request.execute()

Upvotes: 8

Related Questions