javascript - Looking to apply a functioning KB, MB,GB script to specific column in google sheets

Question

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?

question from:https://stackoverflow.com/questions/65884922/looking-to-apply-a-functioning-kb-mb-gb-script-to-specific-column-in-google-she

Answer 1

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