Overview

This script cross-references data between different tabs in a Google spreadsheet, removes the undesired data, and stores the desired data.

Initial Setup

Template Sheet

You will need a Google account to run this program. Navigate to my reformatGoogleSheetsData_DailyReports folder. Right click the dailyReport_SharedGitHub file, and select Add to My Drive. This will create a copy of the file in your google drive.

Script Editor

Open the file from your drive and navigate to the menu bar. Click tools and select Script editor. A new tab should open in the browser titled dailyReport_SharedGitHubScript and it should have three files labeled:

1. code.gs
2. docInfo.gs
3. reusableFunctions.gs

If you can't locate these files, select File > New > Script File > name the file, then copy/paste the corresponding scripts located in the root folder of this GitHub repository.

Example Data

The spreadsheet has example data in place to see how the code operates. Notice the number of rows within each tab:

Tab	Number of Rows
Daily Report:	100
Accounts:	175
User Update:	200

One of the functions this program offers is it updates the data in both the Accounts and User Update tabs. It does this by comparing the information in both tabs with the information in the Daily Report tab. Notice what will happen to the number of rows within each tab after running the function that updates the report:

Tab	Number of Rows
Daily Report:	100
Accounts:	100
User Update:	100

Run the Search

The first time you try to run this app, Google will inform you the app must be approved. Select this google clould help link to learn how to verify the app. You can bypass the previous process by following these steps:

1. Navigate to the menu bar, click Reporting, and select Update Reports

2. A pop-up will appear letting you know authorization is required. Click the button labeled Continue.

3. Select the Google account you wish to use. Then select the link labeled Advanced.

4. Click the link at the bottom. It should be labeled with the name of the script being used to run this app. Unless you rename the script it should be labeled as dailyReport_SharedGitHubScript.

5. Type the word Continue in the text box and click NEXT.

6. Once verified, the app should run. But, if it doesn't, repeat step 1 and that should do the trick.

Modify the script

Look at the following notes to make modifications that will meet your needs:

• To rename the spreadsheet tab names:
  
  
  • Click the drop-down icon on the tab that will be renamed, then click Rename.
  
  
  
  * Navigate to the menu bar. Click tools and select Script editor. A new tab should open in the browser titled dailyReport_SharedGitHubScript. Open the file labeled reusableFunctions.gs. Locate the constants object and rename the values for the corresponding keys for each sheet name.
  
  
• To modify the script:
  
  
  • Navigate to the menu bar. Click tools and select Script editor. Open the file labeled docInfo.gs.
  
  
  * Locate the accountsReport function. This function accepts one arguement (i.e. data from the Daily Reports tab), and modifies data in the Accounts tab.
  
  * Locate the userUpdateReport function. This function accepts one arguement (i.e. data from the Daily Reports tab), and modifies data in the User Update tab.