Google Drive. Report with data from the table. Creating a simple database. Part 1

I think everyone knows about Google Drive (Google Docs), and many probably know about Google Apps Script , if anyone does not know, you can read , in short, this is the API for Google documents. I present another way to use Google Spreadsheet (Tables) and Google Doc (Document), to generate documents with data from the table.

For those who first learned about GAS (Google Apps Script) I advise you to read Getting Started with Google Apps Script

Task: Automate the process of creating documents.
Decision:

1. Open the database, find the necessary data;
2. Create a copy of the template;
3. Fill in the copied template;
')
For data storage we will use Tables, for storage of a template we will use Document.
Document - contains the formatted text of the document for issuing to the client, in which the client data is stored in variables (in my case such variables were used:).

In the tables we will store the data itself in this structure:


For an example of a template, take the text:


A few words about the ID, in the Disk each object has its own ID and you can get it by looking at the browser line when the object is open (Document, Table, Folder):


Below is the code, I think it is quite commented, please forgive my French English. Entry point of the doAction () script:

function doAction() {

var result = ReadBaseData( "31-05-2012" );
//If found data for this date...
if (result != null ) {
var doc = CreateNewDoc( "Act from " + result[0][0]);
FillTemplate(doc, result);
}
}
//inDoc - its a empty template, inData - data for filling template
function FillTemplate(inDoc, inData) {
//Getting count of files in folder
var NUM = DocsList.getFolderById(FOLDER_ID).getFiles().length;

//Replace masks (Don't work [NUM] or $NUM or something else)
inDoc.replaceText( "<NUM>" , NUM + 1);
inDoc.replaceText( "<DATE>" , Utilities.formatDate(inData[0][0], "GMT" , "dd.MM.yyyy" ));

//get first table in document
var table = curDoc.getTables()[0];
for (i=0; i< inData.length; i++) {
var row = table.appendTableRow();
row.appendTableCell(inData[i][1]);
row.appendTableCell(inData[i][2]);
}
curDoc.saveAndClose();

}
function CreateNewDoc(docName) {
//Making copy of blank file
var blankDoc = DocsList.getFileById(BLANK_DOC_ID).makeCopy(docName);
blankDoc.addToFolder(DocsList.getFolderById(FOLDER_ID));

//return working doc and table base
return curDoc = DocumentApp.openById(blankDoc.getId());
}
//get range and sort only for day from parameter
function ReadBaseData(inDate) {
var ssDoc = SpreadsheetApp.openById(BASE_TABLE_ID).getActiveSheet();

//get filled range
var data = ssDoc.getDataRange().getValues();

//create new array with data to inDate,
//for start from 1 because first row is text column headers
var filteredData = new Array( new Array() );
for (i=1; i< data.length; i++) {
data[i][0] = convertDate(data[i][0]);
if (data[i][0] == inDate && data[i][3] == 1) {
filteredData[i-1] = data[i];
}
}
return filteredData;
}

* This source code was highlighted with Source Code Highlighter .

At the end we get a new document in the folder with the Acts.

and filled with data from the table

Thus it is possible to automate the simple issuance of invoices or acts in a small office, without installing application software.

If it is interesting, I can continue in the next article on GUI.

Thanks for attention!