How do you apply a JOIN and FILTER formula to an entire column in Google Sheets using Google Script?

1
votes

I'm very new to Google Scripts so any assistance is greatly appreciated.

I'm stuck on how to apply my formula which uses both JOIN and FILTER to an entire column in Google Sheets.

My formula is: =JOIN(", ",FILTER(N:N,B:B=R2))

I need this formula to be added to each cell in Column S (except for the header cell) but with 'R2' changing per row, so in row 3 it's 'R3', row 4 it's 'R4' etc.

This formula works in Google sheets itself but as I have sheet that is auto replaced by a new updated version daily I need to set a google script to run at certain time which I can set up via triggers to add this formula to my designated column.

I've tried a few scripts I've found online but none have been successful.

2
google-apps-scriptgoogle-sheets
It's always a good idea to share a sample of your spreadsheet so that people can help you with a concrete example. Not sharing a sheet makes people think harder, which sometimes they're not willing to. – Nabnub
Could you please provide more details on sheet that is auto replaced by a new updated version daily? How is the sheet getting replaced? It's through an Apps Script time-based trigger? Please consider sharing the corresponding code if that's the case. – Iamblichus
Hi @Nabnub I have taken a sample from my sheet, please see the link below. docs.google.com/spreadsheets/d/… – Adam Newman
Hi @Iamblichus - The sheet is getting replaced by a third party app (CloudHQ) which overrides the file. – Adam Newman
isnt' it something like this =JOIN(",",FILTER(A2:N2,B2==R2)) – Cooper

2 Answers

2
votes

If you want to solve this using only formulas:

Since your formula is always in the format:

=JOIN(", ",FILTER(N:N,B:B=R<ROW NUMBER>))

and you want to apply it to a very large number of rows, you can use INDIRECT and ROW to achieve a dynamic formula. This answer has a good example on how to use this.

Using formulas you don't risk running into time limits with Apps Script

In practical terms, if you have your data on column A, you can write =ARRAYFORMULA(CONCAT("R",ROW(A2:A))) to get something like this: Generating R<Number> automatically

Your final formula should look like this:

=JOIN(", ",FILTER($N:$N,B:B=INDIRECT(CONCAT("R",ROW($R2)))))

Final Results

Also, you can drag it down to other cells like any other formula!

1
votes

Set the formulas through Apps Script:

You can use setFormulas(formulas) to set a group of formulas to all the cells in a range. formulas, in this case, refers to a 2-dimensional array, the outer array representing the different rows, and each inner array representing the different columns in each specific row. You should build this 2D array with the different formulas, while taking into account that the row index from R should be different for each single formula.

You could do something like this:

function settingFormulas() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var firstRow = 2;
  var column = 19; // Column S index
  var range = sheet.getRange(firstRow, column, sheet.getLastRow() - firstRow + 1);
  var formulas = range.getValues().map((row, index) => {
    let rowIndex = index + firstRow;
    return ["=JOIN(\", \",FILTER(N:N,B:B=R" + rowIndex + "))"];
  });
  range.setFormulas(formulas);
}
  • In this function, the optional index parameter from the method map is used to keep track of the row index, and adding it to the formula.
  • In this function, the sheet name is used to identify which sheet the function has to set the formulas to (in this case, the name's Sheet1). Here I'm assuming that once the sheet is replaced by a newer one, the sheet name remains the same.

Execute this daily:

Once you have this function, you just have to install the time-driven trigger to execute this function daily, either manually, following these steps, or programmatically, by running this function once:

function creatingTrigger() {
  ScriptApp.newTrigger("settingFormulas")
    .timeBased()
    .everyDays(1)
    .create();
}

Reference: