[FIXED] Google Script to replace IMPORTXML(too big xml to import with IMPORTXML)

Issue

I want to import a Google Shopping XML to a Google Spreadsheet, but Google Spreadsheet shows a error:

Error Resource at url contents exceeded maximum size.

I need to import the data (and update it each 6 hours) and I don't know how with this error.

A example of what I using is this:

=IMPORTXML("http://avambu.xtechcommerce.com/datafeeds/google_shopping","//channel/item")

But with a larger XML.

There is a script or another solution for this?

Thanks!

Solution

The best solution I found for now is with a PHP script.

$filexml = 'GoogleProductFeed.xml';
$xml = simplexml_load_file($filexml);
$xml->registerXPathNamespace('g', 'http://base.google.com/ns/1.0');

if (file_exists($filexml))  {    
   $xml = simplexml_load_file($filexml);
   $i = 1;           // Position counter
   $values = [];     // PHP array

   // Writing column headers
   $columns = array('title', 'link', 'description', 'g:availability', 'g:price', 'g:image_link', 'g:product_type',
                    'g:google_product_category', 'g:condition', 'g:identifier_exists', 'g:id');

   $fs = fopen('GoogleProductFeed.csv', 'w');
   fputcsv($fs, $columns);      
   fclose($fs);

   // Iterate through each <item> node
   $node = $xml->xpath('//item');

   foreach ($node as $n) {               
       // Iterate through each child of <item> node
       foreach ($columns as $col) {         
         $values[] = trim($xml->xpath('//item['.$i.']/'.$col)[0]);
       }    
       // Write to CSV files (appending to column headers)
       $fs = fopen('GoogleProductFeed.csv', 'a');
       fputcsv($fs, $values);      
       fclose($fs);  

       $values = [];    // Clean out array for next <item> (i.e., row)
       $i++;            // Move to next <item> (i.e., node position)
   }
}

Thanks for @Parfait for the answer of another question with a better PHP script to do this.

To the script run automatically, I need to use the CRON from a linux server.

Answered By - Diego Gaona