How to select subset of a range in Google Spreadsheet?-CodePudding

I want to extract subset of a range from another range by selecting starting columns and length.

For example, how can I extract B1:C1 from another range A1:D1 by indicating starting columns 2 as in the image attached?

Thanks!

CodePudding user response：

In your question, how about the following answer? In this answer, I used a custom function created by Google Apps Script. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script.

function someKindOfFunction(range, startColumn, length) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const orgRange = sheet.getRange(range);
  const srcRange = orgRange.offset(0, startColumn - 1, orgRange.getNumRows(), length);
  const values = srcRange.getValues();
  return values;
}

When you use this script, for your showing sample Spreadsheet, please put a custom function of =SUM(someKindOfFunction("A1:D1",2,2)) to a cell. By this, 5 is returned.

Testing:

When this script is used for your showing sample Spreadsheet, the following result is obtained.

In this case, the range is given as a string like =SUM(someKindOfFunction("A1:D1",2,2)). Please be careful about this.

Note:

At your showing image, you are using =SUM(someKindOfFunction(A1:D1,startingrow: 2, length: 2)).
- From this, I guessed someKindOfFunction might be a custom function.
- And, from your question, I guessed "startingrow" might be "startingcolumn".

Added:

As another direction, when you use A1:D1 as the range instead of the string value, how about the following sample script? In this sample script, the array is directly used. But, I'm not sure whether this is your expected direction.

function someKindOfFunction(values, startColumn, length) {
  return values.map(r =>
    r.flatMap((c, j) => {
      const start = startColumn - 1;
      return j >= start && j < start   length ? c : [];
    })
  );
}

In this case, when you put a custom function of =SUM(someKindOfFunction(A1:D1,2,2)) to a cell using your sample Spreadsheet, 5 is returned.

References:

CodePudding user response：

This is easily achievable with the OFFSET function:

=sum(offset(Range,,StartCol-1,,Width))

So for your specific example, the expression would be:

=sum(offset(A1:D1,,2-1,,2))