CODEWITHSUNDEEP
google-sheets-api
Sahin D.
Sahin D.

Reputation: 138

Formula delimiter differences on different locales

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

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

Upvotes: 2

Views: 303

Answers (1)

Tanaike
Tanaike

Reputation: 201553

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\")"

Upvotes: 2

Related Questions