I am complete noob to scripts, java and all things code but i need to get a script working on a google spreadsheet of mine.

Its a file inventory and I need to convert raw bytes to KB, MB,GB.

I tried a formatting ajustment on the cells themselves but that gave me incorrect calculations something to do with 1000 vs 1024 values

I have a script that works im just not sure how to get it into my spreadsheet.

(Spare me corretions on my improper use of coding grammar, I am not trying to become a better coder I just want to get this thing working...)

Here's what I want to implement to my google sheet

function formatBytes(bytes, decimals = 2) {
    if (bytes === 0) return '0 Bytes';

    const k = 1024;
    const dm = decimals < 0 ? 0 : decimals;
    const sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB'];

    const i = Math.floor(Math.log(bytes) / Math.log(k));

    return parseFloat((bytes / Math.pow(k, i)).toFixed(dm)) + ' ' + sizes[i];
} 

Can anyone help?

I am complete noob to scripts, java and all things code but i need to get a script working on a google spreadsheet of mine.

Its a file inventory and I need to convert raw bytes to KB, MB,GB.

I tried a formatting ajustment on the cells themselves but that gave me incorrect calculations something to do with 1000 vs 1024 values

I have a script that works im just not sure how to get it into my spreadsheet.

(Spare me corretions on my improper use of coding grammar, I am not trying to become a better coder I just want to get this thing working...)

Here's what I want to implement to my google sheet

function formatBytes(bytes, decimals = 2) {
    if (bytes === 0) return '0 Bytes';

    const k = 1024;
    const dm = decimals < 0 ? 0 : decimals;
    const sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB'];

    const i = Math.floor(Math.log(bytes) / Math.log(k));

    return parseFloat((bytes / Math.pow(k, i)).toFixed(dm)) + ' ' + sizes[i];
} 

Can anyone help?

• javascript
• google-apps-script
• google-sheets
• number-formatting

• function formatBytes(bytes, decimals = 2) { if (bytes === 0) return '0 Bytes'; const k = 1024; const dm = decimals < 0 ? 0 : decimals; const sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB']; const i = Math.floor(Math.log(bytes) / Math.log(k)); return parseFloat((bytes / Math.pow(k, i)).toFixed(dm)) + ' ' + sizes[i]; } – Timi Akindele-Ajani Commented Jan 25, 2021 at 12:42
• Java is to Javascript like ham is to hamster or car to carpet. They just share a common prefix. – Johannes Kuhn Commented Jan 25, 2021 at 14:00

2 Answers 2

Some googling lead me to: https://gist.github.com/loganvolkers/933af8513ed8c2268f59c85a31761a43

Select your cells, go to Format menu, then Number, then Custom Number format and paste the following:

[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB"

Credits to @loganvolkers.

This is how it looks like (applied to the 2nd column)

You can insert KB, MB,GB as a cell format and using sheet functions

By clicking a column and then click on Format > Number > More Formats > Custom number format and fill it with 0.00 KB, 0.00 \MB, 0.00GB (note that character M is special here) adding as many decimals as needed.

Once you've done that go to the KB column and use the following sheet function =ARRAYFORMULA(IFERROR(A2:A/1024)) keep in mind this is just an example and you should change the range. The same goes to MB and GB =ARRAYFORMULA(IFERROR(B2:B/1024)) or =ARRAYFORMULA(IFERROR(A2:A/(1024*1024)))-

As a different approach you can bind your Google Apps Script to your Spreadsheet

You can find how to correctly use Google Apps Script and Sheets here or you can add the Advance Service which allows you to interact with the API itself.

Bear in mind:

• The script should be bound to your Spreadsheet. Otherwise you've got to open it by using ID like var ss = SpreadsheetApp.openById("abc1234567");
• The following functions are useful in your specific case getActiveSheet, getRange, getValue, setValue