Export Product Data

Export product data to a Google Sheet and calculate the ROI of each product.

Start Now!
Export Google Shopping Product data
Export Get started!

This script was written by Martijn Beumer from Producthero. With a few minor adjustments, you can now use this script to export all product data from Google Shopping campaigns to a Google Sheet. This report can then be used as input for your product feed, for example. With the help of this product data you can view the performance per productId. In addition, the script offers the possibility to calculate the ROI of each product in the sheet based on the costs and turnover from Google Ads.

Google Shopping ROI

The calculation of the ROI per product from Google Shopping can serve as input for the campaign structure in Google Shopping. Organizing the Smart Shopping or regular Google Shopping campaigns with Smart Bidding based on ROI can provide more control over the performance and use of budget in your account. This script can automatically calculate the ROI of any product. Please note, if you have a lot of products, it is advisable to put these calculations with a formula in the sheet itself, because otherwise the script will take a very long time.


  • LOG: Indicate whether the script should report the intermediate steps by changing the value to 'true'.
  • DATE_RANGE: Indicate how many days the script has to look back to calculate the CPA.
  • SPREADSHEET_URL: Make a copy of this spreadsheet and enter the URL here to use the results of the script.
  • CALCULATE_ROI: Set to 'true' to calculate the ROI of each product and add this to the sheet. Recommended to add the formula in the sheet itself when dealing with a lot of products.
The script
// Copyright 2023
// Created By: Martijn Beumer
// for Producthero
// Tweaked/Formatted by: Tibbe van Asten
// Last update: 14-06-2022
// Export productdata from Shopping campaigns to a sheet.

var config = {

  LOG : true,
  DATE_RANGE : last_n_days(30),
  // Edit the URL of an empty Google Sheet in here, with '/edit' at the end


function main() {
  var spreadsheet = SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);
  var sheet = spreadsheet.getActiveSheet();
  var from = config.DATE_RANGE[0];
  var to = config.DATE_RANGE[1];

  var report = AdsApp.report(
    "SELECT segments.product_item_id, metrics.clicks, metrics.impressions, metrics.ctr, metrics.conversions, metrics.conversions_from_interactions_rate, metrics.conversions_value, metrics.cost_micros " +
    "FROM shopping_performance_view " +
    "WHERE metrics.impressions > 0 " +
    "AND segments.date BETWEEN '" + config.DATE_RANGE.split(',')[0] + "' AND '" + config.DATE_RANGE.split(',')[1] + "'");
    if(config.LOG === true){
      Logger.log("Initial export complete");
  if(config.CONVERT_MICROS === true){
    const lastRow = sheet.getLastRow();
    const range = sheet.getRange("H2:H"+lastRow);
    const values = range.getValues();
    var newValues = [];

    for (let i = 0; i < values.length; i++) {
      var micro = values[i];
      var normal = micro / 1000000;

  Logger.log("Thanks for using this script!");
} // function main()


function last_n_days(n) {

	var	from = new Date(), to = new Date();
	to.setUTCDate(from.getUTCDate() - n);

	return google_date_range(from, to);

} // function last_n_days()


function google_date_range(from, to) {

	function google_format(date) {
		var date_array = [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate()];
		if (date_array[1] < 10) date_array[1] = '0' + date_array[1];
		if (date_array[2] < 10) date_array[2] = '0' + date_array[2];

		return date_array.join('');

	var inverse = (from > to);
	from = google_format(from);
	to = google_format(to);
	var result = [from, to];

	if (inverse) {
		result = [to, from];

	return result.join(',');

} // function google_date_range()
Show whole script!
Loading Comments
The Experts
Tibbe van Asten Team Lead Performance Marketing
Nils Rooijmans Water Cooler Topics
Martijn Kraan Freelance PPC Specialist
Bas Baudoin Teamlead SEA @ Happy Leads
Jermaya Leijen Digital Marketing Strategist
Krzysztof Bycina PPC Specialist from Poland
How about you? JOIN US!
Sharing Knowledge

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Training &
Contact us!
Adsscripts Training & Workshop