I got an array in a single column. I need to reduce fractional part for big numbers like >= 1, keep 1 symbol of fractional part for numbers <1 and >=0,1, keep 2 symbols of fractional part for numbers <0,1 and >=0,01, keep 3 symbols of fractional part for numbers <0,01 and >=0,001. All values can not be rounded, for user view only. For example:
[33800]->33800; [468]->468; []-> ""; [1170.0000000000002]->1170; [2437.5]->2437; [2762.5]->2762; [322.4]->322; [1430.0000000000002]->1430; [910]->910; [1300]->1300; [52]->52; [0.023]->0,023; [6500]->6500.
I tried to do that but my way is wrong
function recalculate() { const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const sourceValues = sh.getRange(1, 1, sh.getLastRow()).getValues(); const ratio = sh.getRange(1, 2, sh.getLastRow()).getValues(); const targetRange = sh.getRange(1, 3, sh.getLastRow()); let result = []; for (let i = 0; i < sourceValues.length; i++){ result.push([sourceValues[i] * (1 - ratio[i])]) } console.log(result) let numFormat = targetRange.setValues(result); for (i = 0; i < numFormat.length; i++){ switch (numFormat[i] > 0) { case numFormat < 0.1: numFormat[i].setNumberFormat('#,##0.00') ; case numFormat < 1: numFormat[i].setNumberFormat('#,#0.0') ; default: numFormat[i].setNumberFormat('#0'); } } }
Can you explain why and where I’m wrong?
Modified script
function recalculate() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sh = ss.getActiveSheet(); const sourceValues = sh.getRange(1, 1, sh.getLastRow()).getValues(); const ratio = sh.getRange(1, 2, sh.getLastRow()).getValues(); const targetRange = sh.getRange(1, 3, sh.getLastRow()); let result = []; console.log(JSON.stringify(sourceValues)) //log: [[13000],[468],[""],[3900],[3250],[3250],[520],[2600],[910],[1300],[52],[0.023],[6500]] let value; for (let i = 0; i < sourceValues.length; i++){ value = sourceValues[i][0] if (value === "" || value === 0) { result.push([""]) }else{ result.push([value * (1 - ratio[i])])} } console.log(JSON.stringify(result)) //log: [[33800],[468],[""],[1170.0000000000002],[2437.5],[2762.5],[322.4],[1430.0000000000002],[910],[1300],[52],[0.023],[6500]] let numFormat = targetRange.setValues(result); const rangeList = result.reduce((ar, [e], i) => { if (e > 1) ar.push(`C${i + 1}`); return ar; }, []); sh.getRangeList(rangeList).setNumberFormat("#"); ss.setSpreadsheetLocale("fr_CA"); }
Expected result
Advertisement
Answer
- You want to achieve the number format like
1654.123 -> 1654, 23.456 -> 23, 0.43 -> 0,43, 0.02 -> 0,02, 0.037 -> 0,037
in the cells on Google Spreadsheet.- You want to use the values as the number.
- You want to achieve this using Google Apps Script.
Modification points:
- In your scrit,
numFormat
is the range object. Under this situation, whenfor (i = 0; i < numFormat.length; i++){
is used, the for loop is not correctly run (the script in the for loop is not run.), becausenumFormat.length
isundefined
. - When you want to change the decimal operator from
.
to,
, in this case, how about changing the locale of Spreadsheet?- At pnuts’s answer, Canada (French) is proposed as the locale. Ref
Modified script:
When your script is modified, it becomes as follows.
From:
const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
To:
const ss = SpreadsheetApp.getActiveSpreadsheet(); const sh = ss.getActiveSheet();
And
From:
for (i = 0; i < numFormat.length; i++){ switch (numFormat[i] > 0) { case numFormat < 0.1: numFormat[i].setNumberFormat('#,##0.00') ; case numFormat < 1: numFormat[i].setNumberFormat('#,#0.0') ; default: numFormat[i].setNumberFormat('#0'); } }
To:
const rangeList = result.reduce((ar, [e], i) => { if (e > 1) ar.push(`C${i + 1}`); return ar; }, []); sh.getRangeList(rangeList).setNumberFormat("#"); ss.setSpreadsheetLocale("fr_CA");
- From your script, it supposes that the number format of the column “C” is modified.
References:
Added 1:
Although I’m not sure whether this is your current issue, in your script, when the cells are empty in the column “A”, the column “C” might not be the empty. If you want to resolve this issue, how about modifying as follows?
From:
result.push([sourceValues[i] * (1 - ratio[i])])
To:
result.push([sourceValues[i][0] ? sourceValues[i][0] * (1 - ratio[i][0]) : ""]);
- In your script,
sourceValues
andratio
are 2 dimensional arrays.
Added 2:
Unfortunately, I cannot replicate your situation. For example, when your script and values are used like below script, the following result is retrieved.
33800 468 1170 2438 2763 322 1430 910 1300 52 0,023 6500
Sample script:
const result = [[33800],[468],[""],[1170.0000000000002],[2437.5],[2762.5],[322.4],[1430.0000000000002],[910],[1300],[52],[0.023],[6500]]; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sh = ss.getActiveSheet(); const targetRange = sh.getRange(1, 3, sh.getLastRow()); let numFormat = targetRange.setValues(result); const rangeList = result.reduce((ar, [e], i) => { if (e > 1) ar.push(`C${i + 1}`); return ar; }, []); sh.getRangeList(rangeList).setNumberFormat("#"); ss.setSpreadsheetLocale("fr_CA");