form-mulegoogle sheetsgoogle-sheets-addonsgoogle-sheets-custom-function
I have Google Sheets running the add-on Form Mule to automatically send emails with the responses to a Google Form. Using the following formula, I was able to get a nice HTML table to show up in the body of each email.
=RANGETOTABLE(FILTER(D:AH,A:A=A2),$D$1:$AH$1)
This returns a table that looks like this:
I want to try and filter the cells with nothing in them, out, so that only cells with values in them are returned.
How do you create a formula that can be part of a Google Sheets pivot table?
Here is an example:
It is very simple not only because just for illustration but also because Calculated Fields are not the place for complex formulae.
It looks like this issue is due to a bug in the new Google Sheets, but you can use this simple script as a workaround.
In your sheet, go to Tools > Script Editor. Erase the existing code in Code.gs and add this instead:
function setCellAlign(range, alignment) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange(range);
cell.setHorizontalAlignment(alignment);
}
function onFormSubmit() {
setCellAlign("A1:F500", "center");
}
In the second-to-last-line, change F500 to the expected bottom-right cell of your sheet. For example, if your rightmost column is G and you want to hold 1000 responses in your sheet, change the value to G1000 so the second-to-last line is
setCellAlign("A1:G1000", "center");
Save the script project as "AutoAlign"
Within the Script Editor, go to Resources > Current project's triggers. Add a new trigger for onFormSubmit, From the spreadsheet, On form submit, and press "Save."
Your responses should now align automatically to the center. For whatever reason, this script fails to work 1% of the time, but if that happens the next form submission will fix the previous row.
Best Answer
You can include multiple conditions in a filter by using multiplication (which means logical AND). For example,
requires A to be equal to A2 and E to be nonempty and H to be nonempty.
Conditions can also be combined using addition, which stands for logical OR:
requires A to be equal to A2 and that either E or H be nonempty.
If you have a lot of columns (like D:AH), one of which must be nonempty, the formula will have to be somewhat long. Instead of adding lengths as above, one can use concatenation
&to shorten it a bit:Long, but you only have to do it once.
By the way, I used a spreadsheet to generate the long string above, by making a column of letters (say, in A1:A30) and then using
=join(" & ", arrayformula(A1:A30 & ":" & A1:A30)))