Google Sheet Import only visible rows (filtered view) #66384

Hello Chris,
I remember the content of this post was removed and set to resolved when checked last time. We don't get email on post which is edited so this got missed.

Could it be possible to import from a google sheet only the rows that are visible and exclude the ones that are hidden with a filter?. That would be very useful (at least for us) as we do a lot of custom/partial imports of products.

Importing only the visible rows is not possible with current RO CSVI google sheet imports. I see that Google API has an option of setting filters but we are not sure on how that works. You are the first user asking for this feature. If we get more request on this feature we will consider in adding this with future version of RO CSVI.

Hello Chris,
We will look into this and get back with an update.

Hello Chris,
We have developed this feature and attached files with the patch file. You have to

1. Load the attached patch file with RO CSVI Maintenance page.
2. In your import template settings on Source tab set Import hidden rows field to No (By default it is set to Yes). Save the template.
3. In import preview page you should see hidden rows are not loaded.
4. Run the import.

This feature skips hidden rows either set to hidden by user manually or set hidden by filter in google sheet. Please test the feature and let me know on how it goes.

• patch_google_2021_04_07.zip
• 
• 

Hello Chris,
There is a new setting Import hidden rows added to import template on Source tab with the patch. Did you save the setting in your import template? Can you try saving with one import template and see if the error is gone?

Hello Chris,
Please check your server error logs and see if you can find any details on the error. You can post it here so i can check.

Hello Chris,
Two suggestions

1. In your import template settings on Limit tab set Lines per import to 100 or 50, save the template and run the import again.
2. Check what happens if you set 'Import hidden rows' to Yes on Source tab. See if import goes with out any error.

Hello Chris,

2. No change even if i set it to Yes

Ok with Import hidden rows set to Yes the new code sent with the patch file is not processed. So 500 error is not because of patch file. Is the 500 error only with Google imports or with all imports? Which Joomla, VirtueMart and PHP version you are using?

Also check 500 internal server error document and see if any of the solution in the document helps you with the error.

Hello Chris,
Your Joomla, VirtueMart and PHP versions looks good and screenshot shows patch is installed correct. I think it must be something with code. Can you send me your site login and (S)FTP details to check? You can post the details in Site details tab under your reply.

Hello Chris,
Thank you for sending requested details. I have been testing on your site with 500 error and could see the call made for google sheets with new code is causing this problem. I am not sure if there is something different with your google sheet and that is causing the error. To test more on this, please send me the link of your google sheet and provide access to my email tharuna.b@gmail.com. I will set this sheet in my local server for further testing.

You can set Import hidden rows field to Yes and run the import. The error with this condition is solved.

Hello Chris,
Thank you for sharing the google sheet. I am able to reproduce the error with your google sheet. I am looking into it and will keep you updated on the findings.

Hello Chris,
I fixed the cause for 500 error and also to skip the hidden values from Google sheet filter views. Uploaded changes to your site. Import preview looks fine to me with VM Stock Google Sheet Import template. I did not run the import on your site so can you please check and let me know if it works as expected?

Hello Chris,
Google sheets api gets hidden values from Filter views created in the sheet and those are removed from import. May i know how you have hidden the values? Filters shows the below list as hidden SKU values and so values other than that shows up in import preview page.

235156,06-3455-57,06-7720S-SET,08-1003-09,08-1407-12,08B,08T,150188-SET,151007-03C,151007-03C-SET,151510-SET,151574-SET,151869-SET,151883-SET,152046-7-SET,152084-95,152181-01-SET,152211-SET,152245-SET,152477-8-SET,152513-01-SET,152513-03-SET,152517-8-SET,152590-SET,152894-06,170030P,170056-SET,250003M,250450,25872-B-SET,25872-SET,25872-Y-SET,3-1-Connector,300401,75-7663,75-7676,A074-SET,A3007-SET,AGBOVFG,aluminium-bends,aluminium-straight-tubes,aluminized-bends,BC,BL-101,BL-107,BL-114,BL-15,BL12-13,BL154-SET,BL161-SET,BL161VB-SET,BL162-SET,BL167-SET,BL171-SET,BL186-SET,BL187ST-70,BL188-SET,BL189-SET,BL192-SET,BL194-95,BL202-89-SET,BL202VB-89-SET,BL204-SET,BL205-SET,BL206-SET,BL208-SET,BL214-SET,BL215-SET,BL217-SET,BL219-SET,BL22-23,BL220-SET,BL221-SET,BL222-SET,BL226-SET,BL227,BL33-34,casting-bends,CAT104R1,CAT104R2T,CAT125R1,CAT125R2-3,CH102,Clamps,cone-filters-blue-130,cone-filters-blue-65,couplers,CP-1BK,CUT,CZT-Pipe-SET,F101250,FL22,FL7,FL80-90-101,flat-wiper-blades,flexible-hoses,FLT,front-wiper-blades,FVW100150,gaskets,green-replacement-filters,hangers,Header-Wraps-500,heavy-duty-clamps,HH102,hose-clips,HR-002,HR-008,ILP,kart-filters,KAU01,KAU101P,KITR32,KITYR32,KMN,KVB,Lambda-Sensor,LF133,LF133S,LF165-SET,LF166-SET,M039-SET,M044-SET,monster-line,MSH8,perforated-pipes-aluminized,perforated-pipes-ssteel,pipe-connectors,powerflow-filters,rear-wiper-blades,reducers,Rubber-Rings,S8150,SC47-57,Short-Bends,shs,silencers-38,silencers-50,SL102135,SL10245,SL10276,SL1027645,SL1027690,SL10290,small-filters,Sound-Controller,Spiral-Air-Hoses,SSAR,ssteel-cnc-bends,straight-hangers,Titanium-Wraps,TR1003-64,TR1010,TR1012,TR1019-50,TR1020-22,TR1025,TR510-55,TR520,TR540,TR550,TR680,TR700,TR720,TR790,TR800,TR870,TR880,TR890,TR960,TR970,TR980,TR990,TRM1,TRM2,TRM3,trucks-front-wiper-blades,u-clamps,v-bands,vacuum-valves,VC,VSC,VVCL,VVOP,walker,WS-002,WS-107,WSP,Y1-Y2,Y3-Y4,YC,06-3455,RM,RMLS,SRM,SRMLS"

Hello Chris,
I am still seeing the same values as hidden content. Let me look into this more and get back to you with an update.

Hello Chris,
Checking more on the filters i found few things with Google Sheets API

1. Hidden values can be set using Filter views and that is how Google sheets API sends the values as hidden. So if i have to hide values then it has to be created as filter view.
2. If there are more than two filters then the values are conflicted. One filter view works perfect.

I am still looking into the logic with Google sheets with these hidden values and filters. I just want to update you on my findings.

Hello Chris,
I have extended the filter views in Google sheets import. Users can now specify the filter views names to be used in the Google sheet imports.

To use the feature

1. Please load the attached patch file for modified code.
2. Create Filter views in your google sheets for hidden values.
3. In import template settings on Source tab, under google sheet settings give the names of filter views to be used on import. Multiple filter view names can be added by using + button. See attached screenshot.
4. Save the template.
5. Run the import and check.

Let me know on how this modified code goes for you.

• patch_google_2021_04_16.zip
• 

Hello Chris,
I don't have all the template fields set as your template but using your Google sheet and partial-import filter, i can see 3 rows loaded in import preview. See attached screenshot. If there is any issue, please post latest import template XML file to check. To get your template XML file, in RO CSVI maintenance menu select RO CSVI on first option, backup templates in second option, select your import template and download the XML file. Post the XML file.

Hello Chris,
Testing this on your site, i found that the cause for the missing rows is the range set in the import template. I changed setting in your Google import template to range A to Z and all three rows load in import preview. If i extend the range to AX then it shows the first 2 rows. I am looking into it and will let you know on the cause.

Hello Chris,
Haven't got to perfect solution yet and still working on the issue. Will keep you posted.

Hello Chris,
I think i finally fixed the issue with filter views. I did little more improvement to the code. Now when you set the filter name in the template, it is also necessary to set the column header name on which the filter is applied. Google API does not give us this information but just the values that are hidden. In your case the filter is set on product_sku field so set this column name along with filter name. See attached screenshot. I have applied the code on your site and tested the import preview page. It looks good to me. Can you please run the import and check? I would also request you to apply more filters on other columns and check if rows are hidden based on the filters set.

Hello Chris,

Many thanks for the update! Looks good so far for a single column filter, although I cannot get it to work for multiple fiters, how is this supposed to work?

Multiple filters is on multiple columns, create filters for each column with a different name and apply it on the columns. So if first filter has 4 rows and second filter has 2 rows, import will show 7 rows including the column header. From what i noticed is that Google sheet can show only one filter at a time based on the column it is applied. That is what i meant as multiple filters. I hope it is clear now.

I just did a simple test with 2 column filters, see attached. Google sheet shows 5 products but the preview implies 6 (it doesn't take into consideration the 2nd column filter which removes product BL206-SET as it has blank value)

Import preview includes the header column so it shows 6.

I see that you have used filter on filter with product_sku and parent_product_sku columns, in the sense first filter values in product_sku column and then filter values in parent_product_sku column based on filtered rows of product_sku. Is that correct?

Hello Chris,.
Did you get to test the multiple filter views? Please let us know if you have any update on that.

Hello Chris,

Noted what you mean by multiple filters now, it's clear and it's working fine from what I've tested so far!

Thank you for confirming the feature is working :)

That's correct, the multiple filtering as we use it (but within only 1 filter view) allows for further filtering in the already filtered values; this way you have total control on the filtering and final rows to be shown. That would be nice to see in a further update as an ultimate goal, but you have done already a fantastic job so far, many thanks!

Yes i understand your point here. As much as i have seen Google API supports multiple filters on multiple columns so i am not sure if Google API supports this. We will look if this filtering option is available in Google API.

Hello Chris,
The hidden filter code is included in 8.2.0 version so you don't need to install the patch. Google has the details of on which column the filter is applied so we modified the hidden filter field so to ease work for users. Filter view is just a simple select filter field like in the attached screenshot. Do you see this Filter view names field in your template or not?

Hello Chris,
Yes i see filtered rows works as expected. Please send me access to Google sheet and template XML to check. I will have a look at it.

Hello Chris,
Thank you for sending the requested files. I was able to see the issue with your template and Google sheet. The problem is with the range set in your import template. Change the range as A to L in the template, as shown in the attached screenshot and you should see the filter applied. I noticed in your google sheet filter is applied on these range of columns. Try import and let me know.

Hello,

Thanks for the tip; i originally didn't want to include columns A,B,C so that was on purpose. By starting from A column now the import works ok, although i get the unsupported field error ofcourse for columns A,B,C which i can live with that i guess :) (The idea basically is that you don't always need to start from A col)

Those column headers are not VirtueMart database fields and so you see unsupported field. If you want to avoid it, you can remove these columns from the filter and set the range from D column in the template.

Now a thing i noticed is that the import doesn't take into account all columns; i.e. in the template i have set columns from A to BC (file_url). However when i import the last imported col is suspiciously the Z (custom_delete). So from AA afterwards it doesn't take any columns into account it seems. See also attached log. Would appreciate your advice.

Yes i was able to see this issue and fixed it with attached patch file. Please load the patch file and check the import.

• patch_google_2022_05_17.zip

Hello Chris,

Now it should be Universal Parts/Exhausts/Exhaust Sound Control while before it was Universal Parts\Exhausts\Exhaust Sound Control

I guess that was changed recently deliberately or is it a bug?

There was no recent change or neither it is a bug. I see category_path is loaded as Universal Parts/Exhausts/Exhaust Sound Control which is correct. Categories path are separated by / and not .

Hello,
Glad to know your issues are solved. I am closing this thread. Please create a new thread for any new issue.