Google Sheets Useful Codes

 function GetAllSheetsName_1() {

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const shts=ss.getSheets();

  shts.forEach((sht,index)=>{         //index is optional here

    Logger.log(sht.getName() + " " + index)

  })

}

function GetAllSheetsName_2() {

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const shts=ss.getSheets();

  Logger.log(shts.length)

  

  for (let i=0;i<shts.length;i++){

    Logger.log(shts[i].getName())

  }

}

function GetDataRng(){

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const sht=ss.getSheetByName('Sheet4');

  const dRng=sht.getDataRange();

  Logger.log(dRng.getA1Notation());

}

function GetRangeValues(){

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const sht=ss.getSheetByName('Sheet1');

  //const data=sht.getDataRange(); //works like vba sheet1.usedrange.address

  //sheet1.usedrange.address gives the actual data range but

  //sht.getDataRange(); gives range starting from A1 to the last cell address having value.

  const data=sht.getRange('E12').getDataRegion();//works like vba range("A1").currentregion

  Logger.log(data.getA1Notation());

  const values=data.getValues();

  Logger.log(values[3,1]);

}

function GetRangeValues_2(){

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const sht=ss.getSheetByName('Sheet1');

  //const value=sht.getRange('B2').getValue(); //Working

  const range=sht.getRange('A2:B5')

  const values=range.getValues();

  //const values=sht.getRange('A2:B5').getValues();

  Logger.log(values);

  Logger.log(values[1]);

}

function GetRangeValues_3(){

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const sht=ss.getSheetByName('Sheet1');

  const rng=sht.getRange(1,1);

  Logger.log(rng.getValue());

}

function formatMySpreadsheet() {

  // Set the font style of the cells in the range of B2:C2 to be italic.

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheets()[0];

  //var cell = sheet.getRange('B2:C2');

  //cell.setFontStyle('italic');

  Logger.log(sheet.getName());

}

function WriteValues(){

  const ss=SpreadsheetApp.getActiveSheet();

  //ss.getRange("F20").setValue('Terabaap');

  //ss.getRange("F21:G21").setValues([['Tera','Baap']]);

  //ss.getRange("F22:G22").setBorder(true,true,true,true,true,true);

  //ss.getRange("F22:G22").setFormulaR1C1('=Sum(R15C6:R21C7)')//Absolute Reference

  ss.getRange("F22:G22").setFormulaR1C1('=Sum(R[-6]C:R[-1]C)')//Relative Reference

}

function getSheetCodeName(){

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  //const sht=ss.getSheetByName("Sheet1");

  //Logger.log("Id is " + sht.getSheetId());

  const sht=ss.getSheetByName(0);

Logger.log(sheet1.getSheetCodeName());

//Logger.log(sht.getSheetName());

}

function TestRange(){

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const sht =ss.getSheetByName("Sheet1");

  const rng=sht.getRange(1,7,1,5);

  //const rng=sht.getRange(1,7).offset(0,5);

  rng.setBackground('Pink');

}

function CopyToAnotherSpreadSheet(){

  const ss1=SpreadsheetApp.openById('1JzByxjcN8MvgO47Q9UqFFvmxvX0bCeRj57xpuhQlUcA');

  const ss2=SpreadsheetApp.openById('1xoBKDWJ_xwFJ6r0jgNoWEtld80rwkZtmjF1RokOjmwE');

  const sht1=ss1.getSheetByName('Sheet1');

  const rng1=sht1.getRange('A1:B5');

  const data1=rng1.getValues();

  //Copy data to another spreadsheet

  //data1.copyTo(ss2.getSheetByName('Sheet1').getRange('A1'));//Not working

  //https://www.youtube.com/watch?v=8sZot1I7NxU

  const sht2=ss2.getSheetByName('Sheet1');

  const rng2=sht2.getRange('A1:B5');

  rng2.setValues(data1);

  sht2.getRange('A1:B1').setFontColor('white');

  sht2.getRange('A1:B1').setFontStyle('italic');

  sht2.getRange('A1:B1').setFontWeight('bold');

  sht2.getRange('A1:B1').setBackground('grey');

  sht2.getRange('A1:B5').setBorder(true,true,true,true,true,true);

  sht2.autoResizeColumns(1,5); //give starting and end column number

  // Sets the first 5 columns to a width that fits their text.

}

function CopyNPaste2() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('A1:B5').activate();

}

function BBC() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('A:B').activate();

  spreadsheet.getActiveSheet().autoResizeColumns(1, 2);

}

function LuckyMe() {

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const sht=ss.getSheetByName('Sheet4');

  Logger.log(sht.getLastRow());

  Logger.log(sht.getLastColumn());

  Logger.log(sht.getMaxRows());

  Logger.log(sht.getMaxColumns());

}

function LuckyMe2(){

  const ss=SpreadsheetApp.getActiveSpreadsheet();

  const sht=ss.getSheetByName('Sheet1');

  const data=sht.getRange("E12").getDataRegion();

  Logger.log(sht.getRange('E12').getRow());

  Logger.log(data.getLastRow());

  Logger.log(data.getLastColumn());

}