Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

Ask for Help
62

@ThinhDinh thank you. I changed your script to copy from col D all to col R like below:

function fillFormula() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(‘Transactions’);
var lr = sheet.getLastRow();
var fillDownRange = sheet.getRange(lr, 8, 1);
sheet.getRange(“H”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 9, 1);
sheet.getRange(“I”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 10, 1);
sheet.getRange(“J”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 11, 1);
sheet.getRange(“K”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 12, 1);
sheet.getRange(“L”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 13, 1);
sheet.getRange(“M”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 14, 1);
sheet.getRange(“N”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 15, 1);
sheet.getRange(“O”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 16, 1);
sheet.getRange(“P”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 17, 1);
sheet.getRange(“Q”+(lr-1)).copyTo(fillDownRange);
var fillDownRange = sheet.getRange(lr, 18, 1);
sheet.getRange(“R”+(lr-1)).copyTo(fillDownRange);

}

Issue is it was looking good yesterday but today I see last row is empty from col D col R. even if I paste manually, it becomes empty after a few seconds. what should I do?

63

So you want to copy all things from col H to col R down to the next row?

64

Yes that is what I am trying to do

65

What functions do you have in those columns that can’t be set up with an arrayformula?

66

I have INDIRECT formula. I tried to apply array formula to sum others but Inthought it would be easierputting all into one script. Should I use arrah where possible and use script as I noted?

1 Like
67

Whatever you choose to as long as it works! Has your problem been solved today?

68

Actually no. Problem is that it is not stable. It copies down cells but later on I see copied sells empty and then back again.

69

When you say empty cells, does it mean it does not have anything at all or is there a formula but it returns nothing?

70

It is empty. nothing in the cell. I can give you access if you want to check.

71

If you can send it here or in private message then I will have a look later tomorrow. Thank you.

72

Good morning,

I just discovered the magic of a ArrayFormula yesterday in my spreadsheet :-). I’m 40 and it is never too late. I know :slight_smile:

However, every time I put this type Arrayformula =ArrayFormula(IFNA(VLOOKUP(A1:A,‘SEANCE ATP’!A:C,3,false),""))
in a google sheet connected to a Glide Apps the data submitted through my glideapps don’t appear on the google sheet and but sometimes in the glide data table.

Do you have any solution or explanation.
Thank you and by the way it is snowing in Chicago

3 Likes
73

Scroll down to the bottom of the sheet. I’m sure it’s there.

Glide will not fill rows that have a formula in them, but instead add a new row at the bottom of the sheet. This new row then gets populated with the formula automatically. Arrayformulas put the formula in all rows, although it may appear empty. You need to delete all empty rows in this case.

4 Likes
74

Thank you. I found my data and I deleted the blank rows. It works now.

2 Likes
75

Hi there

I’m facing a problem with the use of arrayformula. When ever I am using the arrayformula for a column and after that I try to make an entry from the glideaap, the new row is getting added at the end of sheet leaving so many rows blank. How to avoid this situation?

To avoid this I manually deleted the empty rows. But after this if row with Data is being deleted using glideaap, and new data is entered through aap, it is leaving the deleted row as blank instead of filling the new data in the deleted row. How to solve this issue?

Thanks in advance.

76

Hi @Dibyaranjan_Senapati

Welcome to the community, great to meet you.

Just one quick question - did you actually ‘delete’ the rows or did you just clear or remove the data from within the rows?

Maybe not the answer but thought it worth checking.

77

Actually I created a new tab and entered the arrayformula in the row header.

When I added a data from the aap, it got added at the last row of the sheet.

I deleted all the blank rows so that the entered data will be present in the second row.

78

That’s just how it works and I think is a limitation of how the Google API works. Deleting all the empty rows like you did is the recommend method.

If your arrayformula is simple enough, you can most likely create the functionality within glide and you wouldn’t have to deal with arrayformulas at all.

79

the arrayformula I’m using here is to concatenate two columns and and I’ll be referring the resultant value to another sheet where I’m doing the other mathematical calculations.

Can I do the concatenation in the glide it self and use it to refer to the other sheet as mention above?

80

Yes, you can create a Template column to join multiple column values together. I do that quite often to join multiple values to create a key to link sheets together through a relation column.

5 Likes
81

thank you so much. It worked.

1 Like