Add columns to an existing source of data

Question

I would like to add several columns to an existing source of data displayed in a sheet. But as a data source, the number of rows can increase. I don't want to update the sheet once it is set up, it should be automatic. In those new columns, I would like formulas.

Please find below the result expected.

in blue, columns from data source
in red, my new columns with a formula

How can I do ?

Thank for you help

share a copy/sample of your sheet with included example of desired output — player0, Nov 19 '21 at 22:56
Thank for your reply. First sheet, data source. Second one is made with the first one but with additional formula. The "difficulty" is the data source will become bigger and I don't want to update manually the second sheet every time. I put here the GSheet of what I expect : https://docs.google.com/spreadsheets/d/15GFUHIL2o6wTbRJUgicNVcCTrHvr3U_1CffmZ2hAUpU/edit?usp=sharing — z0om, Nov 22 '21 at 09:55

player0 · Accepted Answer · 2021-11-22T11:14:53.217

1

use in E1:

={"new col 1"; ARRAYFORMULA(IF(C2:C="";;IF((ISNUMBER(C2:C))*(C2:C>=2); 1; 0)))}

use in F1:

={"new col 2"; ARRAYFORMULA(IF(D2:D="";;IF((ISNUMBER(D2:D))*(D2:D>=2); 10; 0)))}

edited Nov 22 '21 at 11:14

answered Nov 22 '21 at 11:04

player0

124,011
12
67
124

I'll test tomorrow. Thank you for your reply ! – z0om Nov 25 '21 at 16:59
It works, you're awesome, even if don't understand everything and especially why the second parameter is empty. – z0om Dec 03 '21 at 08:39

score 0 · Answer 2 · answered Dec 03 '21 at 15:24

I tried with a custom function and to personalize it with another project, it doesn't work...

In this simple case, it works. If not empty, it returns G cells in uppercase :

={"Feature"; ARRAYFORMULA(SI(G2:G="";;IF((NOT(ISEMPTY(G2:G))); UPPERCASE(G2:G); "-")))}

In other case with a custom function, not. It is named "getFeature"

={"Feature"; ARRAYFORMULA(SI(G2:G="";;IF((NOT(ISEMPTY(G2:G))); getFeature(G2:G); "-")))}

When I use it in a classic way, my function works : =getFeature(G7)

Here my custom function :

function getFeature(searchString)  {

  if (searchString === "")  {
    return ""
  }

  var sFeature = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("raw feature")
  var rFeatureCol = sFeature.getRange(2, 1, sFeature.getLastRow(), 1)
  
  array = searchString.split(";")

  for (let i = 0; i < array.length; i++) {
    try  {
      var textFinder = rFeatureCol.createTextFinder(array[i])
      var search_row = textFinder.findNext().getValue()
      return array[i]
    }
    catch {
      // erreur détectée
    }
  }

  return ""
}

Add columns to an existing source of data

2 Answers2