.png&optimizer=image&quality=80&width=280)
Table of Contents
Tags
Welcome!
In this blog we will be learning how to create Google Doc reports from data in Google Sheets using Google Apps Script. We will be using the Google Drive App and Document App to achieve our goal of creating the reports. To automate this process further, we will be using Triggers in Google Apps Script to automatically run the code and generate the reports.
The test case that I will be using for this blog is as follows:
- I have a Google Sheet that contains product details of the product with the id, product name and the initial stock.
- We want to create a Google Doc report from the data in the Google Sheet using Google Apps Script.
So let’s get started.
Sample Google Sheet
This sheet contains the product details that look something like this. If you want to work with the sheet, click here.
Step1: Write the Automation Script
While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:
- Click on Extensions and open the Script Editor.
2. This brings up the Script Editor as shown below.
We have reached the script editor, let's code.
function main(){
//Get the Google Sheet and Values
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Inventory")
const data = sheet.getDataRange().getValues();
We are going to start off by creating a new function main(), inside which we will be accessing the Google Sheet using the SpreadsheetApp. After which we will be getting the sheet by name using the getSheetByName() function, inside which we will be passing the name of the Google Sheet.
Next we will be getting the data using the getDataRange() function, after which we will be getting the values using the getValues() function.
//Get the Google Drive Folder
const folder_id = "1ZSZO2-jnmJoTYFAtqOyMg7duD-d2koPs";
const folder = DriveApp.getFolderById(folder_id);
Once we create the Google Doc we want to store it in a particular folder to keep track of the reports generated. We are going to start out by declaring the folder ID. On declaring the Google Drive ID, we are going to get the folder using the getFolderById() function, by passing the folder id as a parameter.
//Create the Google Doc
const doc = DocumentApp.create("Inventory Report");
const id = doc.getId();
Next we are going to be creating the Google doc using the DocumentApp.create() function, by passing the name of the file as a parameter. After creating the Google Doc, we will be getting the id of the Google Doc using the getId() function.
//Move the Doc to the Folder
var file = DriveApp.getFileById(id);
file.moveTo(folder);
After creating the doc, lets go ahead and transfer the Google Doc to the target folder. To do this we will first be getting the file by id, using the getFileById() function. After this we will be moving it to the folder using the file.moveTo() function inside which we will be passing the folder as a parameter.
//Add values to the Google Doc
var body = doc.getBody();
table = body.appendTable(data);
}
After creating the doc and moving it to the target folder, we will be adding the values to the Google Doc. To do this we will be first getting the body of the Google Doc using the doc.getBody() function.
On getting the body, we will be appending the table from the sheet into the Google Doc suing the appendTable() function inside which we will be passing the data that we extracted.
Step2: Add the Trigger
Now that our code is complete, let's go ahead and add the trigger. This trigger will automate the process of you going and manually running the script at the end of the day.
Let's say your inventory gets updated at 6PM and you have to create a report. This can be done using a trigger. To add a trigger to the script, follow these steps:
- Go ahead and click on Triggers.
2. On clicking Triggers, go ahead and click on Add Trigger.
3. On clicking Add Trigger, you have to enter the following details.
You start out by specifying the function you want the trigger to run. Next we specify the event source, since we want this code to run on or between specific times you have to click on Time-driven. Next we have to specify the type of time based trigger. For this case, we are going to select a Day Timer. In the end you specify the time at which you want the trigger to execute the code.
After making the required changes, go ahead and click on Save.
Step3: Check the Output
Our code is complete and good to go. Lets go ahead and see if our code is working and good to go.
On running the code, it should create a new Google Doc and append the table into it and transfer it to the target folder.
Here you can see that the Google Doc has been created and then transferred to the Target folder.
On opening the Google Doc, you can see that the table has been appended successfully.
Promo- Welcome to Chartmat! Are you tired of struggling to make sense of your data in Google Sheets? Do you want to turn your sheets into powerful apps and dashboards that can help you make better decisions and drive your business forward? Look no further! With Chartmat, you can easily transform your Google Sheets into beautiful and interactive dashboards and apps. Whether you're a small business owner, a marketer, or a data analyst, Chartmat has something for everyone. With our easy-to-use platform and extensive range of features, you'll be able to turn your data into actionable insights in no time. So why wait? Sign up for Chartmat today and start unlocking the full potential of your data! Check out the link given below to know more about charmat!
Conclusion
In this blog we saw how we can create a Google Doc report from data in Google Sheets using Google Apps Script. You can use this to create reports from data in Google Sheets, maybe weekly or even daily, if your data in the Google Sheet constantly changes.
You can get the code from the GitHub link below.
Feel free to reach out if you have any issues/feedback via Chartmat Community.