Getting data ranges info

[Keith Andersen]

Like to share two functions that help me greatly and save me alot of typing and headaches
when tab names are changed.

MY PREFERRED METHOD IS TO RUN FUNCTIONS THROUGH THE SHEETS GID to get > getSheetByName()
I USE THE runByGID() TO MATCH THE GID TO SHEET NAME RETURNING THE PROPER SHEET OBJ
THIS WAY IF THE SHEET NAMES ARE CHANGED, IT WON'T BREAK FUNCTIONS
EACH FUNCTION HAS UNIQUE IDENTIFIERS TO MATCH GID's TO PROPER SHEETS / FUNCTIONS

Here's the function:

/////////////////////////////////////////////////////////////////////////////
//---             GET SHEET OBJECT FOR SPECIFIC SHEET      ---------------
function runByGID(sheetGID){
  //-- RETURNS SHEET OBJECT CONNECTED TO GID
 
  //--- ------------- get workbook ----------------------------------------
  const wb1 = SpreadsheetApp.getActiveSpreadsheet();//--- Spreadsheet workbook
  const sheetsALL = wb1.getSheets();

  for( i in sheetsALL ){
    if( sheetsALL[i].getSheetId() == sheetGID ){
      let sheetName = sheetsALL[i].getSheetName();
      let sheet = wb1.getSheetByName(sheetName);
      return sheet;//returns sheet object matching input GID
    }
  }
 
  return null;
}

I would use it like so:

let sheet = rubByGID(1705833621);

Then I would use sheet to interact with the sheet as normal;

sheet.getRange("A2:D2").getValues();

I also use the formula below to get a data range's info:

start row
start column
number of rows
number of columns
next avaliable data row
headers

by putting only three parameters - sheet, dataStartCell, dataKey

The data key is the column number of your data range that will always have data.
That dataKey column is used to find the length of the data set by finding the first empty
cell going down the column.


///////////////////////////////////////////////////////////////////
//---             RETURN DATA RANGE INFO          ---
function getDataRange1(sheet, dataStartCell, dataKey){
  // sheet is sheet object returned from runByGID()
  // dataStartCell is upper left most cell in data range in A1 notation
  // dataKey is column number guaranteed to have data to count # of data rows

  if(sheet){
    try {
      let startRange = sheet.getRange(dataStartCell);
      let startRow = startRange.getRow();
      let startColumn = startRange.getColumn();
      let MaxColumns = sheet.getMaxColumns();
      let MaxRows = sheet.getMaxRows();
      let headersRange = sheet.getRange(startRow, startColumn, 1, MaxColumns).getValues().flat();
      let dataKeyRange = sheet.getRange(startRow, dataKey, MaxRows, 1).getValues().flat();
     
      let numberColumns = headersRange.findIndex(element =>
              element === "" || element === null || element === undefined);
     
      // If no empty column found, use full length
      if (numberColumns === -1) {
        numberColumns = headersRange.length;
      }

      let numberRows = dataKeyRange.findIndex(element =>
              element === "" || element === null || element === undefined);
     
      // If no empty row found, use full length
      if (numberRows === -1) {
        numberRows = dataKeyRange.length;
      }

      let headers = sheet.getRange(startRow, startColumn, 1, numberColumns).getDisplayValues().flat();
      let nextRow = Number(numberRows) + Number(startRow);
     
      return [startRow, startColumn, numberRows, numberColumns, nextRow, headers];
   
    }catch(error){
      Logger.log('Error in getDataRange: ' + error.message);
      let errorMessage = "⚠️ Process: error occurred in getDataRange(): Check Logger.log for error specifics.";
      return SpreadsheetApp.getActiveSpreadsheet().toast(errorMessage, "⚠️ Error", 15);
    }
  } else {
    let errorMessage = "⚠️ Process: error occurred in obtaining sheet object in function getDataRange(): Check Logger.log for specifics";
    return SpreadsheetApp.getActiveSpreadsheet().toast(errorMessage, "⚠️ Error", 15);
  }
}


As you can see it returns an array of data range info: [startRow, startColumn, numberRows, numberColumns, nextRow, headers];

So then you could call your data like so:

let data = sheet.getRangeData( sheet, "A1", 2);

Then .... myData = sheet.getRange( data[0], data[1], data[2], data[3]).getValues();

It also gives the nextRow - to append data in the next availible data row.

It also gives you the header row data!

Anyway....hope it helps someone.

Cheers

--

My Site: https://www.sites.google.com/view/klaweb/

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[Emerson Maia]

Good morning, very cool, I also like to use it in the same way. I have an object-oriented project tutorial, which has the same idea, congratulations.
Follow the tutorial. 
https://bit.ly/idsheetsmedium

	Expert  Docs Emerson Maia	Goiânia Go Brazil

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BEywwK2W%2BweP4D0yAox-_mAkLv5QT30Hk8pbcHe5QjFHA%40mail.gmail.com.

[Keith Andersen]

Thanks!

Is there an english version of your tutorial?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CADH-QrDKG7aOqhnMieNB%2Bc5%3D_Rgdk-zO%3D%3DHHjx4YyTvgYjh0vw%40mail.gmail.com.

[Emerson Maia]

When you are on the page, if you right click you will be able to translate it into your language.

	Documentação especializada Emerson Maia	Goiânia Go Brasil
		+55 62 991911266

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BHeoV%3Dtmgw10pNxduMgavMqtrLGAkbLTqSp21ezBTx1Mg%40mail.gmail.com.