This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap
Hello Learners,
The purpose of this article is to explain how Google Docs can be generated from Google Spreadsheet data using Google Apps Script.
We should understand how development can help and ease our task before looking into it.
What are the benefits of this script and development?
We will see some of the useful tasks that this script can perform.
- Students’ marks can be generated from Google Spreadsheet data.
- House allocation letters can be generated from Spreadsheet data.
- This script can also generate invoices for customers.
- Workshop certificates can be generated and sent to students.
This script can be used to make many more similar things as required.
In order to develop this project, we need the following things:
- Create a Google Document with the required data as a template.
2. Creating a Google Spreadsheet and saving the data.
3. Create a Google Drive folder in which the document will be saved.
4. Write the Google Apps script to generate the Google Documents using the data.
Following are the steps to create a Google Apps script that generates Google Documents
We will follow the same process we followed in our previous articles regarding Google Apps Script development with Google Spreadsheet data.
We are generating a document for college student information.
Step 1: Create Google Spreadsheet and save data, in this sheet data can be collected using Google Form or may fill manually. Sample Google Spreadsheet may look like this.
Step 2: Now, create Google Document with replacement tags (here we are using tags like — timestamp, name, picture, from_year, to_year, college_name), which will be used as template while generating documents.
Step 3: We have Google Spreadsheet and Document template ready, now open google script editor and start following steps to write the script.
Step 3.1: This fillData() function get the data from the getData() function in the JSON object format, which helps us to replace the tags we have defined in the document template.
In fillData() function we have defined, Spreadsheet id, document id, which can be copied from the document URL bar.
Then we read the id and body of the document to replace the tags with our spreadsheet data using script.
After Replacing data with the tags, we identified our folder using folder id, which can also be copied from URL bar once you open folder in Google Drive and move the document file after converting in pdf to that folder.
function fillData() {try {var ss = SpreadsheetApp.openById("YOUR_SPREADSHEET_ID");
var sheet = ss.getSheetByName("main");
var parseJson = getData(sheet);var templateId = 'YOUR_DOCUMENT_TEMPLATE_ID';
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
var doc = DocumentApp.openById(documentId);
var body = doc.getBody();
if(parseJson.timestamp !="") {body.replaceText("<<timestamp>>", parseJson.timestamp);body.replaceText("<<name>>", parseJson.name);body.replaceText("<<picture>>", parseJson.picture);body.replaceText("<<from_year>>", parseJson.from_year);
body.replaceText("<<to_year>>", parseJson.to_year);
body.replaceText("<<college_name>>", parseJson.college_name);DriveApp.getFileById(documentId).setName(parseJson.name + "_" + parseJson.timestamp);
doc.saveAndClose();
var source = DriveApp.getFileById(documentId)
var blob = source.getAs('application/pdf');var file = DriveApp.createFile(blob);
var pdf_file_id = file.getId();
file.setName(source.getName() + '.pdf');
//folder id in which file will be saved
var folder = DriveApp.getFolderById('YOUR_FOLDER_ID');
var folderToMoveFile = isFolderExists(folder);
file.moveTo(folder);
var status = "success";
return status;
}
}
catch(err) {status = err;
}
}
Step 3.2: We have defined getData() function in the Step 3.1, which has to be defined with script in order to get the data in JSON object format. Let’s see the script for that below.
function getData(sheet){
var jo = {};
var dataArray = [];
// collecting data from 2nd Row , 1st column to last row and last // column sheet.getLastRow()-1var rows = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();for(var i = 0, l= rows.length; i<l ; i++)
{ var dataRow = rows[i];
var record = {};
record['timestamp'] = dataRow[0];
record['name'] = dataRow[1];
record['from_year'] = dataRow[2];
record['to_year']=dataRow[3];
record['college_name']=dataRow[4];
dataArray.push(record);
} jo = dataArray;
var result = JSON.stringify(jo);
return jo;
}In the above script, we read the data from the Google Spreadsheet and converted it to the JSON objects, and returned to the fillData() function.
Once you run the fillData() function, the script will be executed and the required task will be completed.
I hope this article will help you understand how to generate document using Google Spreadsheet with the help of Google Apps Script. If you have any questions, please leave a comment. I would be happy to assist you.
How to create documents from Google Spreadsheet data using Google Apps Script was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap
Dilip Kashyap | Sciencx (2022-02-27T18:53:02+00:00) How to create documents from Google Spreadsheet data using Google Apps Script. Retrieved from https://www.scien.cx/2022/02/27/how-to-create-documents-from-google-spreadsheet-data-using-google-apps-script/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.