Get A1Notation of rectangle areas on spreadsheet

Question

I want to get A1Notation of rectangle areas on spreadsheet. Sample of rectangle on spreadsheet is below image.

I want to get A1Notation of cells of value "1". I don't want to get "B2, C2, D2, B3, C3, D3, B4, C4, D4". I want to get "B2:D4". My tried script is

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var result = [];
for (var i = 0; i < data.length; i++){
  for (var j = 0; j < data[i].length; j++){
    if (data[i][j] == 1) {
      var range = sheet.getRange(i + 1, j + 1).getA1Notation();
      result.push(range);
    }
  }
}
Logger.log(result);

This script returns "B2, C2, D2, B3, C3, D3, B4, C4, D4". sheet.getDataRange().getA1Notation() is "A1:D4". Positions of cells of "1" are not always the same. My question is

When positions of cells of "1" make a rectangle area, are there any ways to retrieve the rectangle area as A1Notation?
- At sample, I want to get "B2:D4" from spreadsheet or from "B2, C2, D2, B3, C3, D3, B4, C4, D4".

I checked Puzzle: Find largest rectangle (maximal rectangle problem) and links. But I cant understand the way to apply this to my script. Thank you so much for your time.

Edit

These are samples. I get areas in size order.

   A B C D E

1  0 0 0 0 0
2  0 1 1 1 0
3  0 1 1 1 0
4  0 1 1 1 0
5  0 0 0 0 0

I need "B2:D4".

   A B C D E

1  1 1 1 0 0
2  0 1 1 1 0
3  0 1 1 1 0
4  0 1 1 1 0
5  0 0 0 0 0

I need "B2:D4" and "A1:C1".

   A B C D E

1  0 1 1 1 1
2  0 1 1 1 1
3  0 0 0 1 1
4  0 0 0 0 1
5  0 0 0 0 1

I need "B1:E2", "E3:E5", "D3:D3".

There is not a simple method. If you can be sure that the edges of the table have no gaps, you can use [`Range#getNextDataCell()`](https://developers.google.com/apps-script/reference/spreadsheet/range#getNextDataCell(Direction)) to help find bounding edges, but you still have to implement logic of your own to store the related cell indices and construct the A1 string from them. — tehhowch, May 08 '18 at 06:01
@tehhowch Yes. I think so. But I still cannot find the algorithm to update my script. — Elsa, May 08 '18 at 06:26
Please add the clarification made on the [comment](https://stackoverflow.com/questions/50226192/get-a1notation-of-rectangle-areas-on-spreadsheet#comment87471524_50226600) to [this answer](https://stackoverflow.com/a/50226600/1595451) and edit the title accordingly. — Rubén, May 09 '18 at 16:44
@I'-'I I'm sorry for confusing. I updated my answer. If you see unclear, tell me. — Elsa, May 17 '18 at 01:42
@Rubén I'm sorry for confusing. I updated my answer. If you see unclear, tell me. — Elsa, May 17 '18 at 01:42

score 0 · Answer 1 · answered May 08 '18 at 05:43

0

You can modify your function as follows :

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var result = [];
for (var i = 0; i < data.length; i++){
  for (var j = 0; j < data[i].length; j++){
    if (data[i][j] == 1) {
      var range = sheet.getRange(i + 1, j + 1).getA1Notation();
      result.push(range);
    }
  }
}
Logger.log(result[0] + ":" + result[result.length-1]);

Maybe it's not optimized, because you're iterating over whole data range, but it's hard to tell what the optimized way without knowing your data structure, need in details.

answered May 08 '18 at 05:43

Umair Mohammad

4,489
2
20
34

1

This is good --- for a single table. If there were a table in `B2:D4`, and another in `G1:J3`, your output would be `G1:D4` – tehhowch May 08 '18 at 05:49
That why I mentioned `it's hard to tell what the optimized way without knowing your data structure` – Umair Mohammad May 08 '18 at 06:18
@Umair Thank you for your answer. Sorry. You misunderstand my question. **Positions of cells of "1" are not always the same.** When "B2, C2, D2, B3, C3, D3, B4, C4, D4" and "A1, B1, C1" have "1", your answer returns "A1:D4". But this is not a rectangle area. For example when "A1, B1, C1, B2, C2, D2, B3, C3, D3, B4, C4, D4" have "1", can you update your answer to get a rectangle area and get "B2:D4"? – Elsa May 08 '18 at 06:25
3

When "B2, C2, D2, B3, C3, D3, B4, C4, D4" and "A1, B1, C1", the answer should be 2 areas: "A1:C1" and "B2:D4", or 3 areas "A1", "B1:C4", "D2:D4. @Elsa, your Q is not clear to me. – Max Makhrov May 08 '18 at 08:47
Yes @MaxMakhrov you're correct, this is the reason why I couldn't update my answer. :) – Umair Mohammad May 08 '18 at 08:48
@Max Makhrov I'm sorry for confusing. I commented **When "B2, C2, D2, B3, C3, D3, B4, C4, D4" and "A1, B1, C1" have "1", your answer returns "A1:D4".** I got "A1:D4" from Umair's answer. Actually, I need logic of ``"A1:C1" and "B2:D4"``. I updated my answer. If you see unclear, tell me. – Elsa May 17 '18 at 01:42
@Umair I'm sorry for confusing. I commented **When "B2, C2, D2, B3, C3, D3, B4, C4, D4" and "A1, B1, C1" have "1", your answer returns "A1:D4".** I got "A1:D4" from your answer. I updated my answer. If you see unclear, tell me. – Elsa May 17 '18 at 01:42

Get A1Notation of rectangle areas on spreadsheet

Edit

1 Answers1