1

I am trying to automate chart creation in google sheets for which I need to convert a column data from KB to GB. How can this be done using google apps script?

strongpoint
  • 59
  • 2
  • 8
  • Divide by 1024? – TheMaster Jan 25 '19 at 08:50
  • I want to code this in the apps script and not directly apply formula to every google sheet for conversion. – strongpoint Jan 25 '19 at 08:59
  • 2
    So code it. Is there anything stopping you? – TheMaster Jan 25 '19 at 09:18
  • @TheMaster I feel _exactly_ as you do and for the same reasons. However, there are zero examples of this process as it applies to Google Sheets. Dozens of examples for javascript and other languages, but none that can be applied as a template (so to speak) for Google Docs. It has to be done sooner or later, and my answer is submitted for that reason only. – Tedinoz Jan 26 '19 at 07:35
  • @Tedinoz IMO, This doesn't require a template. I believe OP should've at least attempted to code something before being given a ready made answer. This doesn't mean I don't respect your answer. But > run into his first problem > bang his head a little while to try and figure out >run into next problem> repeat gives birth to a developer. Cheers. – TheMaster Jan 26 '19 at 10:32
  • I worry if the goal was making numbers similar, maybe for simple math(?), it isn't helped by the intermediate goal of making OP a programmer first. It's akin to investing in the stock market and step 2 is 'invent time machine'. – user2066657 Feb 16 '21 at 22:22

1 Answers1

0

There are dozens of examples in StackOverflow, StackExchange and the internet generally of how to convert size in bytes to KB, MB, GB in JavaScript. However there aren't, as best as I can tell, any examples that are specific to Google sheets. This code is offered as a way of documenting the methodology for Google Sheets. Hopefully it will be something that future users can use/adapt rather than raise new (and duplicate) questions.

Credit: Aliceljm - Correct way to convert size in bytes to KB, MB, GB in JavaScript

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);
}

Data Layout

Data layout for conversion of kilobytes

Tedinoz
  • 5,911
  • 3
  • 25
  • 35