How to upload products via spreadsheet

In this article we look at how you can use a GraphQL mutation to manage product uploads via spreadsheet or CSV.

  5 minute read  

What you’ll learn

  • The bulk product upload process
  • How to use GraphQL to automate that process

Product Uploads

You can manually bulk upload (and download) products using a spreadsheet or csv file as described here using the Seller Portal.

For product creation this involves:

  • Downloading a spreadsheet / csv template
  • Populating the spreadsheet with Product data
  • Uploading the spreadsheet
  • Waiting for the batch import job to complete

For product updates this involves:

  • Exporting your products to a spreadsheet / csv
  • Updating the spreadsheet with Product data (retaining the Product IDs)
  • Uploading the spreadsheet
  • Waiting for the batch import job to complete

The process we describe in this guide details how to automate parts of this flow using the GraphQL API, ultimately however it is still this same process you’ll be working with .

Using GraphQL to automate uploads

Both create and update follow similar patterns, so we’ll focus only on product updates as this is a more frequently executed use-case.

Download existing products

In this example, we already have products for a given seller, so we’ll download those products so we can update them. To do this via GraphQL we’ll use the advertSpreadsheetDownload mutation as shown below:

Mutation

mutation downloadExistingProducts(
 $input: AdvertSpreadsheetDownloadMutationInput!
) {
 advertSpreadsheetDownload(input: $input) {
  errors {
   field
   messages
  }
  export {
   processedCount
   id
   status
   seller {
    businessName
   }
   file {
    id
    filename
    size
    url
   }
  }
 }
}

Variables

{
  "input": {
    "format": "CSV"
  }
}

You can provide more input parameters to the mutation as documented here. In this example we are just specifying that we want the .csv file format.

Running this mutation will result in the scheduling of a file export job, and return a payload similar to the following:

{
  "data": {
    "advertSpreadsheetDownload": {
      "errors": null,
      "export": {
        "processedCount": 0,
        "id": "SW1wb3J0RXhwb3J0LTEw",
        "status": "PENDING",
        "seller": {
          "businessName": "Steptoe & Son"
        },
        "file": null
      }
    }
  }
}

Some points to note:

  • As we ran this mutation with a Seller API key, the download is for that seller.
  • Looking at the state returned for the job (PENDING), the processed count (0) and the file detail (null), the expected response for this mutation is not a file download itself, but rather the creation of an export job.

We can check the status of this export job by running the importExports query:

Query

query ($pageSize: Int, $endCursor: String) {
 importExports(first: $pageSize, after: $endCursor) {
  totalCount
  pageInfo {
   hasNextPage
   endCursor
  }
  nodes {
   id
   createdAt
   className
   expectedResultCount
   processedCount
   status
   hasErrorsToCsv
   file {
    id
    filename
    url
   }
  }
 }
}

Variables

{
  "pageSize": 10,
  "endCursor": null
}

This will return the imports and exports we have:

{
  "data": {
    "importExports": {
      "totalCount": 1,
      "pageInfo": {
        "hasNextPage": false,
        "endCursor": "NQ"
      },
      "nodes": [
        {
          "id": "SW1wb3J0RXhwb3J0LTEw",
          "createdAt": "2024-09-09T10:28:20+10:00",
          "className": "Export::AdvertsToSpreadsheet",
          "expectedResultCount": 10,
          "processedCount": 10,
          "status": "FINISHED",
          "hasErrorsToCsv": false,
          "file": {
            "id": "QXR0YWNobWVudC01NQ==",
            "filename": "adverts_export_1725841700.csv",
            "url": "https://marketplacer.imgix.net/z6/C7s7OJ7Zwmg0mVAVI-OZTWUh8.csv?dl=adverts_export_1725841700.csv&s=c5933ab7b74ccd78cb673e509c02115b"
          }
        }
      ]
    }
  }
}

Looking at this payload we can see that export has completed successfully and we’ve been provided with a link to our product CSV file.

Updating product info

While you could programmatically augment the spreadsheet, and more especially the data contained within, this is not within the scope of the Marketplacer API, so we won’t cover that operation here. You can of course also manually update the spreadsheet with your amendments.

Uploading the product spreadsheet

Once you have made the necessary amendments to the product file, ensure it is saved ready for upload.

We upload the product file using the advertSpreadsheetUpload mutation, which accepts the spreadsheet file as a Base64 encoded string.

Base64 encoding the spreadsheet is outside the scope of this guide, however we provide an example of how to do this here.

An example of how to call this mutation is shown below:

Mutation

mutation uploadProduct($input: AdvertSpreadsheetUploadMutationInput!){
	advertSpreadsheetUpload(input: $input){
		status
		advertCount
		variantCount
		import{
			id
			failedCount
			hasErrorsToCsv
			processedCount
			status
			expectedResultCount
		}
		errors{
			field
			messages
		}
	}
}

Variables

For readability purposes we have shortened the value of dataBase64 considerably - this holds the value of the base64 encoded file.

{
  "input": {
    "spreadsheet": {
      "filename": "2024_products.csv",
      "dataBase64": "77u/QWQgSUQsQ3VzdG9..."
    }
  }
}

The result of running this mutation will be a payload similar to the following:

{
  "data": {
    "advertSpreadsheetUpload": {
      "status": 200,
      "advertCount": null,
      "variantCount": null,
      "import": {
        "id": "SW1wb3J0RXhwb3J0LTEx",
        "failedCount": null,
        "hasErrorsToCsv": false,
        "processedCount": 0,
        "status": "PENDING",
        "expectedResultCount": null
      },
      "errors": null
    }
  }
}

As with downloading the products file, this mutation is not expected to return the results of the import immediately. You can query imports and exports as before using the importsExports query to obtain the status.

This would yield a result similar to the following:

{
  "data": {
    "importExports": {
      "totalCount": 2,
      "pageInfo": {
        "hasNextPage": false,
        "endCursor": "Ng"
      },
      "nodes": [
        {
          "id": "SW1wb3J0RXhwb3J0LTEx",
          "createdAt": "2024-09-09T14:12:05+10:00",
          "className": "Import::AdvertsFromSpreadsheet",
          "expectedResultCount": 10,
          "processedCount": 10,
          "status": "FINISHED",
          "hasErrorsToCsv": false,
          "file": {
            "id": "QXR0YWNobWVudC01Ng==",
            "filename": "2024_products.csv",
            "url": "https://marketplacer.imgix.net/nM/0lGoe_Ey2WviTc6FfjnRn-Q5w?dl=2024_products.csv&s=ec8c35173d26b80f3e9e8662a1da291e"
          }
        },
        {
          "id": "SW1wb3J0RXhwb3J0LTEw",
          "createdAt": "2024-09-09T10:28:20+10:00",
          "className": "Export::AdvertsToSpreadsheet",
          "expectedResultCount": 10,
          "processedCount": 10,
          "status": "FINISHED",
          "hasErrorsToCsv": false,
          "file": {
            "id": "QXR0YWNobWVudC01NQ==",
            "filename": "adverts_export_1725841700.csv",
            "url": "https://marketplacer.imgix.net/z6/C7s7OJ7Zwmg0mVAVI-OZTWUh8.csv?dl=adverts_export_1725841700.csv&s=c5933ab7b74ccd78cb673e509c02115b"
          }
        }
      ]
    }
  }
}

The number of products in the file, along with any other running jobs may impact the time taken for the import to start and complete.