0

I'm trying to append cells with hyperlink to a spreadsheet file by following the instructions here https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#celldata

A hyperlink this cell points to, if any. This field is read-only. (To set it, use a =HYPERLINK formula in the userEnteredValue.formulaValue field.)

The problem is that some formulas has multiple parameters that delimited by comma. But delimiters are different on spreadsheet that has different locales- like Turkey as locale. The delimiter on Turkey locale has settled as semicolon not comma. I didn't check if delimiters are different on different locales.

After I tried to add link as formulaValue, the result looks like this on spreadsheet that has Turkey locale:

https://user-images.githubusercontent.com/5789670/77210180-61581500-6b11-11ea-9302-81dcf84256f8.png

and this is from a spreadsheet that has United States locale:

https://user-images.githubusercontent.com/5789670/77210238-8e0c2c80-6b11-11ea-9eb8-ea82fdc869d2.png

Both spreadsheets has same formulas and only difference is just this (compared to a blank spreadsheet)

https://user-images.githubusercontent.com/5789670/77210339-cc095080-6b11-11ea-8805-92b3f6c59b0b.png

It's not like possible for me to track/identify all the configuration for delimiter on different locales. I just simply finding a way to generate hyperlink formula without having delimiter issues.

Something like a function 

.getDelimiter("Europe/Istanbul")

or a field in properties to understand what type of delimiter has used on the target spreadsheet file

// SpreadsheetProperties
"properties": {
  "title": string,
  "locale": string,
  "timeZone": string,
  "formulaDelimiter": string, // read-only
  ...
}

Environment details

  • OS: Ubuntu 18.04
  • Node.js version: v12.13.0
  • npm version: 6.13.7
  • googleapis version: ^48.0.0
Steps to reproduce
  1. Have two different spreadsheets that has United States and Turkey locales.
  2. Use following data to append cell with batchUpdate API
{
    "requests": [
        {
            "appendCells": {
                "fields": "*",
                "rows": [
                    {
                        "values": [
                            {
                                "userEnteredFormat": {},
                                "userEnteredValue": {
                                    "formulaValue": "=HYPERLINK('https://google.com','20006922')"
                                }
                            }
                        ]
                    }
                ],
                "sheetId": 111111
            }
        }
    ]
}

Making sure to follow these steps will guarantee the quickest resolution possible.

Thanks!

Original issue is on Github. Can be found here: https://github.com/googleapis/google-api-nodejs-client/issues/1994

Sahin D.
  • 88
  • 2
  • 6
  • https://stackoverflow.com/q/73767719/5632629 – player0 Sep 19 '22 at 14:13
  • Does this answer your question? [Locale differences in Google Sheets (documentation missing pages)](https://stackoverflow.com/questions/73767719/locale-differences-in-google-sheets-documentation-missing-pages) – player0 Aug 11 '23 at 21:33

1 Answers1

1

In your case, how about this modification?

Issue and workaround:

When the comma , is used like "formulaValue": "=HYPERLINK('https://google.com','20006922')" to the locale which uses the semicolon ;, when the formula is put using the batchUpdate method of Sheets API, the comma is used without replacing. By this, such error occurs.

On the other hand, when the semicolon is used as the delimiter instead of the comma to the local which uses the comma, when the formula is put using Sheets API, the semicolon is automatically replaced with the comma. By this, no error occurs.

From above situation, how about the following modification? In this case, also I replaced ' to ".

From:

"formulaValue": "=HYPERLINK('https://google.com','20006922')"

To:

"formulaValue": "=HYPERLINK(\"https://google.com\";\"20006922\")"
halfer
  • 19,824
  • 17
  • 99
  • 186
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks for the suggestion Tanaike. Currently my app works with more than 11 different locales and it is not a constant number. It keeps increasing in time. So the ultimate solution is to solve programmatically. Since the error is not occurs/returns after the batchupdate API request, I know that there is no other way than opening files on browser or requesting to check for all rows and cells to find an error one by one or in loop. I know this seems like an odd situation but here I am, finding a way :/ – Sahin D. Mar 25 '20 at 08:31
  • @Sahin D. Thank you for replying. I apologize that my proposal was not useful for your situation. – Tanaike Mar 25 '20 at 22:37