Google表格-使用Google脚本将整个列中的数据从KB转换为GB

通常，在StackOverflow，StackExchange和Internet中有许多示例，说明如何在JavaScript中将字节大小转换为KB，MB，GB。但是，据我所知，没有任何特定于Google表格的示例。提供此代码是记录Google表格方法的一种方式。希望将来的用户可以使用/调整而不是提出新的（和重复的）问题。

信用：Aliceljm-在JavaScript中将字节大小转换为KB，MB，GB的正确方法

function so54360760() {

  //complex calculation; assume Bytes in column A; use calculation to return MB, GB, etc

  // Setup spreadsheet and target sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("so_54360760");

  // setup some variables
  var k = 1024;
  var decimals = 3;
  var dm = decimals <= 0 ? 0 : decimals || 2;
  var sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB'];
  var convertedcolumn = [];

  // get the last row in column A
  var aRange = sheet.getRange("A1:A");
  var AVals =  aRange.getValues();
  var ALastRow = AVals.filter(String).length;
  //Logger.log("DEBUG: The last row in A = "+ALastRow);//DEBUG

  // Loop thought the values in column A - start at row 2 (assuming a header in row 1)
  for (i=1;i< ALastRow;i++){
    // Logger.log("DEBUG: i = "+i+", value = "+AVals[i][0]); //DEBUG
    var convert = []; // clear the value of the array for the row

    // calculate the converted value and push to an array
    if(AVals[i][0] == 0){
      convert.push('0 Bytes'); 
    }
    else {
      var x = Math.floor(Math.log(AVals[i][0]) / Math.log(k));
      var xy = parseFloat((AVals[i][0] / Math.pow(k, x)).toFixed(dm)) + ' ' + sizes[x];
      convert.push(xy); 
    }

    // now push the row value to an array that can be used for the column
    convertedcolumn.push(convert);
  }

  //define the destination range
  var bRange = sheet.getRange(2, 2, ALastRow-1);

  // update the range with the array values
  bRange.setValues(convertedcolumn);
}

数据布局