---
title: "Cleanup exported data automatically with Apps Script and advanced formulas in Google Sheets"
author: "Sourabh Choraria"
date: 2020-02-21T01:48:04+00:00
canonical_url: https://pulse.appsscript.info/p/2020/02/cleanup-exported-data-automatically-with-apps-script-and-advanced-formulas-in-google-sheets/
markdown_url: https://pulse.appsscript.info/p/2020/02/cleanup-exported-data-automatically-with-apps-script-and-advanced-formulas-in-google-sheets.md
categories:
  - "Sheets"
  - "Solutions"
tags:
  - "@TechandEco"
  - "Medium"
description: "You may be exporting data from an accounting tool into an .XLS format (Excel spreadsheet) every month, and then manually searching, cleaning, and replacing values. More specifically you probably spend a lot of time finding..."
---

# Cleanup exported data automatically with Apps Script and advanced formulas in Google Sheets

[![](https://miro.medium.com/max/1050/0*svO3nxUHJHkOQLBH)](https://medium.com/@TechandEco/cleanup-exported-data-automatically-with-apps-script-and-advanced-formulas-in-google-sheets-4f77cd8361ca)

> You may be exporting data from an accounting tool into an .XLS format (Excel spreadsheet) every month, and then manually searching, cleaning, and replacing values. More specifically you probably spend a lot of time finding and replacing duplicates, and performing advanced operations such as a [VLOOKUP](https://support.google.com/docs/answer/3093318?hl=en) or a [QUERY](https://support.google.com/docs/answer/3093343?hl=en) function in order to pull data from other sheets or digging through records that use unfriendly date timestamps.

In this article [TechandEco**](https://medium.com/@TechandEco) walks us through how to automate this monthly task by using a Google macro and a custom menu, and invite you to try it out yourself. In the future, you can convert your Excel into a Google Sheet by dragging it or uploading it into a folder in your Google Drive>then second click the file to view its options > open as > Google Sheet.

Source: *[Cleanup exported data automatically with Apps Script and advanced formulas in Google Sheets*](https://medium.com/@TechandEco/cleanup-exported-data-automatically-with-apps-script-and-advanced-formulas-in-google-sheets-4f77cd8361ca)

---

*Published by [Sourabh Choraria](https://pulse.appsscript.info/p/author/scriptgs/) on [Pulse](https://pulse.appsscript.info/). Original article: [https://pulse.appsscript.info/p/2020/02/cleanup-exported-data-automatically-with-apps-script-and-advanced-formulas-in-google-sheets/](https://pulse.appsscript.info/p/2020/02/cleanup-exported-data-automatically-with-apps-script-and-advanced-formulas-in-google-sheets/)*