Useful gas functions for google spreadsheets

GoogleAppsScripts
#1

USEFUL GAS FUNCTIONS FOR GOOGLE SPREADSHEETS

Reference: https://developers.google.com/apps-script/

F1 - Add functions into menu 

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('RTA')
   .addItem('Run All', 'runAll')
   .addItem('Import Data', 'importCSVFromWeb')
   .addItem('Split Data', 'copyToLIST')
   .addItem('Order Sheets by Straid', 'orderSheet')
   .addToUi();
}

F2 - Use BetterLog to insert log into spreadsheet

Add MYB7yzedMbnJaMKECt6Sm7FLDhaBgl_dE in Resources > Libraries
Reference: https://github.com/peterherrmann/BetterLog

function betterLog() {
    try {
        Logger = BetterLog.useSpreadsheet('1m7uBcRVRExuPINKA2c5j7jtMlf8stgeDrXL456DFHk');
    } catch (e) {
        e = (typeof e === 'string') ? new Error(e) : e;
        Logger.severe('%s: %s (line %s, file "%s"). Stack: "%s" . While processing %s.',
            e.name || '', e.message || '', e.lineNumber || '', e.fileName || '', e.stack || '', e.processingMessage || '');
        throw e;
    }
}

F3 - Import CSV data from weblink via API 

function importCSVFromWeb() {
   betterLog();
   // Provide the full URL of the CSV file.
   var csvUrl = "https://pinka.is.number.one/api/download/datamodel?token=PINKA&dm_name=pinka_db&format=csv";
   var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
   var csvData = Utilities.parseCsv(csvContent);

var ss = SpreadsheetApp.openById(‘1NVOttx-KnXbPINKAH9yp6PINKAdOk5ISgK_12328E’);

var sheet = ss.getSheetByName(‘Total’);

Logger.log(‘Importing CSV data from weblink into Sheet Total’);


sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

Logger.log(‘Imported CSV data from weblink into Sheet Total’);


}

F4 - Get Col Index by Cell Value 

function getColIndexByName(sheet, nameCol) {
   var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
   var values = range.getValues();

for (var row in values) {

for (var col in values[row]) {

if (values[row][col] == nameCol) {

return parseInt(col);

}

}

}

}

F5 - Get Row Index by Cell Value 

function getRowIndexByName(sheet, nameRow) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
    var values = range.getValues();

for (var row in values) {
    for (var col in values[row]) {
        if (values[row][col] == nameRow) {
            return parseInt(row);
        }
    }
}



}

F6 - Convert Col Index to Col Letter 

function getColumnLetter(column) {
    var temp, letter = '';
    while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
    }
    return letter;
}

F7 - Copy whole sheet 

function copyToLIST() {
 for (var v = 0; v < nameList.length; v++) {
  objList[v] = source.getSheetByName(nameList[v]);
  var name = nameList[v].concat('_copy');
  objList[v].copyTo(target).setName(name);
  Logger.log('Copied Sheet from %s to %s of Spreadsheet %s', nameList[v], name, nameDist);
 }
}

F8 - Copy values from sheet to sheet 

function copySheet() {

betterLog();
var ss = SpreadsheetApp.openById('1_r91LMpp453mbPINKAKi4jf39_dqLXHS7XFS-iZG2k');
var dest = SpreadsheetApp.openById('1_r91LMppPINKAtXOLoKi4jf39_dqLXHS7XFS-iZG2k');
var vname = 'Vietnamese';
var sheetTmp = ss.getSheetByName('Tmp');
var sheetv = dest.getSheetByName(vname);
var lastRow = sheetTmp.getLastRow();
var rangeNewTmp = sheetTmp.getRange(1, 1, lastRow + 1, 28)
rangeNewTmp.copyValuesToRange(sheetv, 1, 28, 1, lastRow + 1);
Logger.log('Copied values from Sheet %s to Sheet %s', 'Tmp', vname);



}

F9 - Rearrange order of sheets 

function orderSheet() {
    betterLog();
    var ss = SpreadsheetApp.openById('1_r91LMppDiPINKAOLoPINKA9_dqLXHS7XFS-iZG2k');
    var sheets = ss.getSheets();
    if (sheets.length > 1) {
        for (var i in sheets) {
            var nameSheet = sheets[i].getName();
            if (nameSheet == 'Total') {
                ss.setActiveSheet(ss.getSheetByName('Total'));
                ss.moveActiveSheet(1);
                Logger.log('Reordered sheet %s', 'Total');
            } else if (nameSheet == 'Vietnamese') {
                ss.setActiveSheet(ss.getSheetByName('Vietnamese'));
                ss.moveActiveSheet(2);
                Logger.log('Reordered sheet %s', 'Vietnamese');
            } else if (nameSheet == 'North') {
                ss.setActiveSheet(ss.getSheetByName('North'));
                ss.moveActiveSheet(3);
                Logger.log('Reordered sheet %s', 'North');
            } else if (nameSheet == 'South') {
                ss.setActiveSheet(ss.getSheetByName('South'));
                ss.moveActiveSheet(4);
                Logger.log('Reordered sheet %s', 'South');
            } else if (nameSheet == 'Log') {
                ss.setActiveSheet(ss.getSheetByName('Log'));
                ss.moveActiveSheet(5);
                Logger.log('Reordered sheet %s', 'Log');
            }
        }
    }
}

F10 - Delete sheets 

function delSheet() {
    betterLog();
    var ss = SpreadsheetApp.openById('1_r91LMppDi3mbPINKAKi4jf39_dqLXHS7XFS-iZG2k');
    var sheets = ss.getSheets();
    if (sheets.length > 1) {
        var tmpSheet = ss.getSheetByName('Tmp');
        if (tmpSheet) {
            var delSheet = ss.setActiveSheet(tmpSheet);
            ss.deleteSheet(delSheet);
            Logger.log('Deleted sheet %s', 'Tmp');
        }
    }
}

F11 - Set data validation based on list of items 

function validateList() {
    betterLog();
        var colIndex13 = getColIndexByName(sheetOld, "CapDiaDiem") + 1;
        var colLetter13 = getColumnLetter(colIndex13);

    var cells13 = sheetOld.getRange(2, colIndex13, rows - 1, 1); // CapDiaDiem
    var rule13 = SpreadsheetApp.newDataValidation()
        .requireValueInList(['Thôn', 'Xã', 'Huyện', 'Tỉnh'], true)
        .setAllowInvalid(false)
        .setHelpText("Vui lòng chỉ chọn lựa chọn có sẵn trong danh sách CapDiaDiem!")
        .build();

    cells13.setDataValidation(rule13);
    var help13 = cells13.getDataValidation().getHelpText();
    Logger.log('Created data validation rule [%s] for Range 13 (%s%s:%s), Sheet %s of Spreadsheet %s',
        help13, colLetter13, '2', colLetter13, old, nameDist);



}

F12 - Set data validation based on range 

function validateRange() {
    betterLog();
        var colIndex8 = getColIndexByName(sheetOld, "TinhChat") + 1;
        var colLetter8 = getColumnLetter(colIndex8);
        var cells8 = sheetOld.getRange(2, colIndex8, rows - 1, 1); // TinhChat
        var range8 = objList[2].getRange('F2:F');
        var rule8 = SpreadsheetApp.newDataValidation()
            .requireValueInRange(range8, true)
            .setAllowInvalid(false)
            .setHelpText("Vui lòng chỉ chọn lựa chọn có sẵn trong danh sách TinhChat!")
            .build();
        cells8.setDataValidation(rule8);
        var help8 = cells8.getDataValidation().getHelpText();
        Logger.log('Created data validation rule [%s] for Range 8 (%s%s:%s), Sheet %s of Spreadsheet %s',
            help8, colLetter8, '2', colLetter8, old, nameDist);
}

F13 - Clear conditional format rules 

function clearFormatRules() {
    sheet.clearConditionalFormatRules();
 Logger.log('Deleted conditional format rule');
}

F14 - Clear formatting 

function clearFormatRules() {
    sheet.clearFormats();
 Logger.log('Deleted formatting');
}

F15 - Set conditional formatting 

var sheet = dest.getSheetByName(s_name);
var rows = sheet.getMaxRows();
var cols = sheet.getMaxColumns();

var colIndex1 = getColIndexByName(sheet, ‘Trạng thái gọi’) + 1;

var colLetter1 = getColumnLetter(colIndex1);


var range = sheet.getRange(12, 1, rows - 12, colIndex4 - 1);

var range2 = sheet.getRange(12, colIndex4, rows - 12, 1);

Logger.log(‘Sheet %s’, s_name);


var formula1 = ‘=AND(OR(’

.concat(’$’).concat(colLetter1).concat(‘12=“PV thành công”,’)

.concat(’$’).concat(colLetter1).concat(‘12=“PV trực tiếp TC”),’)

.concat(‘OR($’).concat(colLetter2).concat(‘12=“Đã chuyển tiền”,’)

.concat(’$’).concat(colLetter2).concat(‘12=“Không nhận tiền”),’)

.concat(’$’).concat(colLetter3).concat(‘12=“Đã nhập”)’);


var rule1 = SpreadsheetApp.newConditionalFormatRule()

.whenFormulaSatisfied(formula1)

.setBackground("#9D9D9D") // Grey

.setRanges([range])

.build();


Logger.log(‘Set Conditional Format for Grey Case’);

Logger.log(formula1);

F16 - Set Query Formula 

function setQueryFormula() {
  var v = 'PINKA';
  var vname = 'PINKA01';  
  var sheetv = dest.getSheetByName(vname);
  var lastRow = sheetTmp.getLastRow();
  var rangeTo = sheetv.getRange(1, 1, lastRow + 1, 9)
  sheetTmp.getRange("A1").setFormula('=QUERY(Total!A:I, "select A,C,D,E,I,F,G,H where D='.concat(v).concat('")'));
  Logger.log('Set Formula for Sheet %s', 'Tmp');
  var rangeNewTmp = sheetTmp.getRange(1, 1, lastRow + 1, 9)
  // rangeNewTmp.copyTo(rangeTo, SpreadsheetApp.CopyPasteType.PASTE_VALUES);
  rangeNewTmp.copyValuesToRange(sheetv, 1, 9, 1, lastRow + 1);
  Logger.log('Copied values from Sheet %s to Sheet %s', 'Tmp', vname);
}

F17 - Set Sharing permission of the file to COMMENT only for ANYONE_WITH_LINK 

function setSharing() {
    var url_source = SpreadsheetApp.openById("1zInuSeV3LPINKAE1Achw1ZmIKu9G3ML0oAn3L3ZNzc"); // DiaDiem
    var url = url_source.getSheetByName('URL');

var rowIndex = getRowIndexByName(url, "PINKA") + 1;
var colIndex = getColIndexByName(url, "url") + 1;

var url1 = url.getRange(rowIndex, colIndex).getValue(); // get sublink value of District
var fileID = url1;
var file = DriveApp.getFileById(fileID);
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.COMMENT);



}


1 Like