I’m trying to retrieve a range of formulas/values from a sheet with official Google JS API, such that if cell contains a formula rather than a constant value, formula takes precedence over formula result. Everything worked (for a while) with this code:
const result = (await api.spreadsheets.values.get({
spreadsheetId: SPREADSHEET_ID,
range: `${SHEET_NAME}!${RANGE}`,
dateTimeRenderOption: 'FORMATTED_STRING',
valueRenderOption: 'FORMULA',
})).data.values || [];
Given this table
A | B | |
---|---|---|
1 | name | value |
2 | some_name_1 | 0.33 |
3 | some_name_2 | 0.66 |
4 | some_name_3 | =B2+B3 |
and range A2:B
, the code above would correctly produce the following result:
const result = [
['some_value_1', '0.33'],
['some_value_2', '0.66'],
['some_value_3', '=B2+B3'],
];
However, once I changed constant numeric values to greater precision, Google would still round them to 2 decimal places on my own behalf… I.e., if I have this table
A | B | |
---|---|---|
1 | name | value |
2 | some_name_1 | 0.333 |
3 | some_name_2 | 0.666 |
4 | some_name_3 | =B2+B3 |
I would get the following (incorrect) result:
const result = [
['some_value_1', '0.33'],
['some_value_2', '0.67'],
['some_value_3', '=B2+B3'],
];
What I did try: changing the formatting of individual cells/the whole columns/the entire sheet to have more decimal places, and trying the same, but formatting everything as plain text
. Both didn’t do anything.
At this point I’m out of ideas…