removing blank lines

EmilySMassey

Is there a way to remove blank lines when combining multiple google sheets on a schedule?

asifsaroor

Yes, there are several ways to remove blank lines when combining multiple Google Sheets on a schedule:

Using Google Apps Script: You can write a Google Apps Script to programmatically merge multiple sheets and remove blank lines. You can use the getValues() method to retrieve the data from each sheet, iterate through the rows, and exclude rows that are empty. Then, you can write the cleaned data to a new sheet.
Using QUERY function: You can use the QUERY function in Google Sheets to filter out blank rows when combining data from multiple sheets. For example, if you’re using the IMPORTRANGE function to pull data from multiple sheets into a single sheet, you can wrap the IMPORTRANGE function inside a QUERY function and use a WHERE clause to exclude blank rows.
Using FILTER function: Another approach is to use the FILTER function in Google Sheets to remove blank rows from the combined data. You can apply the FILTER function to the array of data pulled from multiple sheets and specify the condition to filter out empty rows.
Using Google Sheets Add-ons: There are also third-party Google Sheets add-ons available that offer advanced data manipulation and cleaning features. You can explore add-ons like “Sheetgo” or “Data Everywhere” that provide functionality to merge sheets and remove blank rows automatically.
Manual Cleanup: If the data volume is manageable, you can manually remove blank rows after combining the sheets. You can use built-in Google Sheets features like filtering or sorting to identify and delete blank rows from the combined dataset.

EmilySMassey

The question was specific to SheetGO. I’m using it, but I cannot find a step in the process to delete blank lines. Any help is appreciated.

Karoline from Sheetgo

EmilySMassey Hi Em,
When you merge your files, Sheetgo typically removes all blank lines and only retains rows that contain data. As part of a test, could you please remove any blank lines from your sources and then run the workflow again? This will help us ;)

EmilySMassey

As I went to respond that I can’t manually remove the blank lines because there are formulas, I realized that was the answer. Even though they’re “Blank” the formula is there and that’s why it’s still pulling them in. ugh…thank you!

Karoline from Sheetgo

EmilySMassey Depending on the formula that you’re using you can add a conditional like “If it’s blank, leave it blank”. Let me know if you need help to set up this.

EmilySMassey

In the spreadsheet?
Here’s are examples of the existing formulas…. the lines show blank unless the conditions are met.
=IF(I1582<>"“,VLOOKUP(I1582,LOCATIONS,9,FALSE),”")
=IFS(K1581="“,”",K1581>0.9, “FULL-TIME”, K1581<1, “PART-TIME”)

What about check boxes or data validation? Would that cause Sheetgo to think the lines aren’t really blank?

Karoline from Sheetgo

EmilySMassey In this case I’d go for an array formula. You can include it on the second row or even on the header and it works for the entire column. It will make your sheet lighter and will get rid of the blank rows.

=ARRAYFORMULA(IF(I1582<>"“, VLOOKUP(I1582:I1585, LOCATIONS, 9, FALSE), ”"))
you just have to adjust to your actual range like
=ARRAYFORMULA(IF(ISBLANK(I2:I), "", VLOOKUP(I2:I, LOCATIONS, 9, FALSE)))

EmilySMassey

I appreciate that and typically would use an array. Unfortunately I have folks with varying skillsets and we kept having problems so I gave up and copied down formulas so if they do type over it it doesn’t jack up all the data! :)