Google Apps Script - percentage formatting and arrays

Question

I am perplexed by the following.

I have data on a google spreadsheet that I am pulling into an array. I am doing some modifications on the data and then putting it back into the spreadsheet. My numbers are formatted like this on the spreadsheet: 00.00% When they come into the array they are formatted like this 0.0, and when I put them back on the spreadsheet into 00.00% format, they are being divided by 100. So what started out as 20.00% becomes 0.20% when I put the data back.

So, I thought I'd multiply all the numerical values by 100 before I put them back on the sheet, which seems like it should be an unnecessary step.

My question is whether I could be doing something differently with how I'm handing the data in the array, or is multiplying every value by 100 my only option here?

Mogsdad Mogsdad · Accepted Answer · 2013-05-05T15:37:12

This is a bug that has been known for a long time:

Issue 472: setValue(s) fails for percent formatted cells.

Visit and star it, maybe it will get more attention.

If you're reading and writing values from percent-formatted cells, you must recalculate the percentage values.

This function uses the format values for a range to apply the *= 100 operation on any cell that's a percentage:

/**
 * Adjusts the values for elements in values array if the
 * corresponding format element is a percentage.
 * Value array is changed in place, as well as returned.
 */
function percentAdjust(values,formats) {
  for (var row = 0; row < values.length; row++) {
    for (var col = 0; col < values[0].length; col++) {
      if (formats[row][col].indexOf('%') !== -1) {
        // Adjust any percent formatted values
        values[row][col] *= 100;
      }
    }
  }
  return( values );
};

function test () {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var values = rows.getValues();
  var formats = rows.getNumberFormats();

  rows.setValues(percentAdjust(values,formats));
}

Google Apps Script - percentage formatting and arrays

2 Answers