Are there any utility methods that can be used to convert cell ranges and addresses?

[John]

For example, how can one easily convert "Sheet1!K7:AA:12" to an array of col/row indexes?

With output like: 

[{colIndex: 10, rowIndex:6}, {colIndex: 10, rowIndex:7}, {colIndex: 10, rowIndex:8}, ...] 

or anything similar to that.  Basically, allowing you to easily iterate through the cells in an address range programmatically.

Just wondering if a method like that is already accessible or if I should manually code it.

[Sangeetha Priya Murugan]

Hi John,

Your requirement can be achievable in our spreadsheet by using the getRangeIndexes method as like as below.

  

function getAddress() {

  let cell: string = spreadsheet.getActiveSheet().selectedRange; // to get selectedRange

  let cellIdx: number[] = getRangeIndexes(cell); // to get cell range indexes

  let cellCollection = [];

  let cellObj = {};

  console.log(cellIdx);

  for (let i = cellIdx[0]; i <= cellIdx[2]; i++) {

    // for row iteration

    for (let j = cellIdx[1]; j <= cellIdx[3]; j++) {

      // for column iteration

      cellObj = { rowIndex: i, colIndex: j };

      cellCollection.push(cellObj);

    }

  }

  console.log(cellCollection);

}

 

For your convenience we have prepared the sample based on our suggestion. In this we have converted the selected range to indexes and iterated that index to get the cell index objects in button click event. Please find the link below.

Sample Link: https://stackblitz.com/edit/gnok26?file=index.ts

Could you please check the above details and get back to us, if you need any further assistance on this.

Regards,

Sangeetha M

[John]

That's perfect.  Thank you.

[Gayathri KarunaiAnandam]

Hi John, 

    

We are happy to hear that your requirement has been fulfilled. Please feel free to contact us if you need any further assistance on this. 

Regards, 

Gayathri K 

[John]

Curious, where can I find the documentation on the  getRangeIndexes ​method and other methods like this?  Perhaps there are other useful methods that I can use so that I'm not reinventing the wheel.  I haven't been successful in locating the help for these (in javascript).

[Sangeetha Priya Murugan]

Hi John,

We have checked your reported suggestion and we will consider this in our documentation site. And we will refresh the documentation in any of our upcoming releases. We appreciate your patience until then.

Regards,

Sangeetha M