Creating a typical spreadsheet - questions.

[dawidbak]

Hi, we decided to use your package for a commercial project. Could I ask you for some tips where to look for some things? I'm a backend developer and I'm just entering your world, so please be understanding. I am talking about:

1. Fill handle - can I somehow set that after double-clicking, the following rows will automatically populate themselves and increasing the value for example from A1 to A2??
2. By selecting some cells I can somehow extract the range and transform it to e.g. "A1:C3"? We currently do this manually by typing.
3. Hints for the formula list, is there an easier workaround or will I have to play around with property "data" in the cell? I mean for example when user type '=' then there is a list of formulas to select.
4. We thought about extracting range of cells and we did something like this:

export default function GetCellsRange(inputText: any): [number, number] {
  let rowCount: number = 0;
  let columnCount: number = 0;
  const columns: number[] = [];
  const rows: number[] = [];
  const rawValues = inputText.substring(
    inputText.indexOf("(") + 1,
    inputText.indexOf(")")
  );
  // A1: AA2
  const values = rawValues.split(":");
  // [A1, AA2]
  for (let i = 0; i < values.length; i += 1) {
    let column: number = 0;
    let row: string = "";
    const element = values[i].toUpperCase().trim();

    for (let j = 0; j < element.length; j += 1) {
      const elementChar = element[j];
      const unicodeNumber = elementChar.charCodeAt(0);
      if (unicodeNumber >= 65 && unicodeNumber <= 90) {
        if (j > 0) {
          column += 25;
        }
        column = column + unicodeNumber - 65;
      }
      if (unicodeNumber >= 48 && unicodeNumber <= 57) {
        row += `${elementChar}`;
      }
    }
    columns.push(column);
    rows.push(parseInt(row, 10) - 1);
    column = 0;
    row = "";
  }
  columnCount = columns[1] - columns[0];
  rowCount = rows[1] - rows[0];
  return [columnCount, rowCount];
}

And then we create an array with the cell indexes:

export function ExtractValuesFromRange(
  data: any[],
  columns: number,
  rows: number,
  indexes: string[]
): number[] {
  const result: number[] = [];
  for (let col = 0; col <= columns; col += 1) {
    for (let row = 0; row <= rows; row += 1) {
      const dataRow = data[row];
      const cellValue = parseFloat(dataRow[indexes[col]]);
      result.push(cellValue);
    }
  }
  return result;
}

Could we use something to make it more efficient? We are not necessery looking for ready-made solutions but for advice, thank you very much in advance for your help.

[jassmith]

Hey David, just wanted to make sure you know I saw this. I will try to get you an answer soon but it might be a bit, personal life has flared up this week :)

[dawidbak]

@jassmith Thank you, i will wait :)

[maakle]

Any updates on this? @jassmith

What did you end up doing in the end @dawidbak? I am also currently evaluating this library for a project of mine. One requirement would be that a user can use basic formulas like e.g. SUM, AVG and basic math operators. Is this possible with this library? I didn't find any examples in the docs.

[BrianHung]

It is possible to use glide-data-grid as the front-end component, but I wouldn't expect it to support formulas or calculations. Those should be third-party e.g. hyperformula or fast-formula-parser. You will need to build your own state management library, and integrate it in.

[jassmith]

ah woops lost track of this, thanks for the ping.

1. I do not understand what that means.
2. Sure that is what onGridSelectionChanged provides you in the range property
3. You will need a custom cell so you can do your magic inputty thing
4. What am I looking at!?

[maakle]

Follow up question from me regarding 3. Does that mean I need to build a custom cell in order to do the sum of all rows above? Or do you have plans to integrate formulas and basic math operators as well e.g. SUM, MINUS, AVG etc.? (Perhaps it also already is integrated and I have missed it. But didn't find examples in the Storybook or docs)

Or would you consider supporting something like Hyperformula?

[jassmith]

Hyperformula is not MIT license compatible, so it is not able to be integrated into GDG. That said GDG is not trying to make a spreadsheet, you should more think of it as a major component to the frontend of such a product.

[BrianHung]

I can try answer this as I'm actually working on a spreadsheet built on top of glide-data-grid (gdg).

1. Fill-handle: you will need to ask for custom behavior here as I've found gdg's fill handle behavior a bit limiting. I think @maakle is asking that the fill-handle behave like in google sheet or excel, where fill handle will create cell content for filled cells based on the current grid selection. Right now, gdg only supports a basic copying: filled cells have the same cell content as the grid selection. Ideally, there is an onFill callback that has (gridSelection, fillSelection) so that developers can integrate it into a third party library (see for example https://hyperformula.handsontable.com/api/classes/hyperformula.html#ranges)
2. This is gridSelection. You will need to transform it into A1 notation yourself.
3. Build a custom cell with a custom editor. I saw a good editor the other day: https://github.com/luizzappa/codemirror-app-spreadsheet
4. This is unrelated to glide-data-grid. You will need to build a formula parser or try to find a library. Hyperformula is good.