André Fecteau
André Fecteau

Reputation: 140

ListBox will not populate with new API

For some reason when you run the app it runs perfectly except for the listbox will not populate. I tried to change it to a while looking thinking it was interator but for some reason the listbox will not populate with the folders that you have in your Drive. Any help would be appreciated, thanks!

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*
Template Generator By: Andre Fecteau - [email protected]
Original Code From: [email protected] (Found in the template gallery by searching "Templates" It is the first One.
Major Help from: Serge Insas On Stack Overflow (He did most of the work.)
Link 1: http://stackoverflow.com/questions/18147798/e-undefined-google-script-error
Link 2: http://stackoverflow.com/questions/18132837/have-a-listbox-populate-with-every-folder-in-mydrive
How To Use:
First: each column is designated in your Template by {Column Letter} for example for column A it would be {A}
Second: You can change this, but your Template must be in a folder called "Templates." This folder can be anywhere in your drive.
Third: Click "Generate Documents Here!" Then click "Export Row to Document"
Fourth: Type in the row you want to export. Chose your Folder Path. Click Submit.
NOTE ON FOURTH STEP: If you want your number to skip the header row add a +1 to line 32.
This would mean if you typed "2" in the row box it actually exports row 3. I took this out because it can get confusing at times.
NOTE: Line 71 you can edit the word "Templates" to whatever folder you saved your Template into.
NOTE: Line 36 you can edit to change what comes up in the name of the file that is created. To do this just edit the column letter in the following piece: "+Sheet.getRange('E'+row).getValue()+"
You can then delete any other columns you do not want by deleteing the section of code that looks like the following: '+Sheet.getRange('D'+row).getValue()+'
Feel free to edit this code as you wish and for your needs. That is what I did with the original code.
So there is no reason I should restrict what others do with this code.

Bug Fix Log:
* changed row: 32 to remove the +1 6/18/2014
* changed row: 40 to remov e the row-- 6/18/2014
* changed row: 68 to update to DriveApp because of Google API Update - 4/21/15
* changed row: 68 to update to getFoldersByName and getFilesByType to update to new Google API - 4/21/15
*/
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function generateDocument(e) {
        var template = DriveApp.getFileById(e.parameter.Templates);
        Logger.log(template.getName());
        var Sheet = SpreadsheetApp.getActiveSpreadsheet();
        var row = Number(e.parameter.row) //+1; // Remove the // in this line next to the +1 to skip headers
        Logger.log(row);
        var currentFID = e.parameter.curFID;
        Logger.log(currentFID);
        var myDocID = template.makeCopy(Sheet.getRange('B' + row).getValue() + ' - ' + Sheet.getRange('E' + row).getValue() + ' - ' + Sheet.getRange('D' + row).getValue() + ' - ' + Sheet.getRange('X' + row).getValue()).getId();
        var myDoc = DocumentApp.openById(myDocID);
        var copyBody = myDoc.getActiveSection();
        var Sheet = SpreadsheetApp.getActiveSpreadsheet();
        //row--; // decrement row number to be in concordance with real row numbers in sheet
        var myRow = SpreadsheetApp.getActiveSpreadsheet().getRange(row + ":" + row);
        for (var i = 1; i < Sheet.getLastColumn() + 1; i++) {
            var myCell = myRow.getCell(1, i);
            copyBody.replaceText("{" + myCell.getA1Notation().replace(row, "") + "}", myCell.getValue());
        }
        myDoc.saveAndClose();
        var destFolder = DriveApp.getFolderById(currentFID);
        Logger.log(myDocID);
        var doc = DriveApp.getFileById(myDocID); // get the document again but using DriveApp this time...
        doc.addToFolder(destFolder); // add it to the desired folder
        doc.removeFromFolder(DriveApp.getRootFolder()); // I did it step by step to be more easy to follow
        var pdf = DriveApp.getFileById(myDocID).getAs("application/pdf");
        destFolder.createFile(pdf); // this will create the pdf file in your folder
        var app = UiApp.getActiveApplication();
        app.close();
        return app;
    }
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function getTemplates() {
        var doc = SpreadsheetApp.getActiveSpreadsheet();
        var app = UiApp.createApplication().setTitle('Generate from template');
        // Create a grid with 3 text boxes and corresponding labels
        var grid = app.createGrid(5, 2);
        grid.setWidget(0, 0, app.createLabel('Template name:'));
        var list = app.createListBox();
        list.setName('Templates');
        grid.setWidget(0, 1, list);
        var folderTemplate = DriveApp.getFoldersByName("Templates");

        while (folderTemplate.hasNext()) {
            var folder = folderTemplate.next();
            Logger.log(folder.getName());
        };
        var allMyFilesByType = folder.getFilesByType(MimeType.GOOGLE_DOCS);
        while (allMyFilesByType.hasNext()) {
            var file = allMyFilesByType.next();
            Logger.log(file.getName());
        };
        grid.setWidget(1, 0, app.createLabel('Row:'));
        var row = app.createTextBox().setName('row');
        row.setValue(SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getRow());
        grid.setWidget(1, 1, row);
        var curFN = app.createTextBox().setText('MyDrive/').setName('curFN').setId('curFN').setWidth('400');
        var curFID = app.createTextBox().setText(DriveApp.getRootFolder().getId()).setName('curFID').setId('curFID').setVisible(false);
        var listF = app.createListBox().setName('listF').setId('listF').addItem('Please Select Folder', 'x');
        grid.setText(2, 0, 'Type Path:').setWidget(2, 1, curFN).setText(3, 0, 'OR').setText(4, 0, 'Choose Path:').setWidget(4, 1, listF).setWidget(3, 1, curFID);
        var folders = DriveApp.getFolders();
        while (folders.hasNext()) {
            var folder = folders.next();
            Logger.log(folder.getName());
        };
        var handlerF = app.createServerHandler('folderSelect').addCallbackElement(grid);
        listF.addChangeHandler(handlerF);
        var panel = app.createVerticalPanel();
        panel.add(grid);
        var button = app.createButton('Submit');
        var handler = app.createServerClickHandler('generateDocument');
        handler.addCallbackElement(grid);
        button.addClickHandler(handler);
        // Add the button to the panel and the panel to the application, then display the application app in the Spreadsheet doc
        panel.add(button);
        app.add(panel);
        doc.show(app);
    }
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function folderSelect(e) {
        var app = UiApp.getActiveApplication();
        var currentFN = e.parameter.curFN;
        var currentFID = e.parameter.listF;
        Logger.log(currentFID);
        var listF = app.getElementById('listF');
        var curFN = app.getElementById('curFN');
        var curFID = app.getElementById('curFID');
        if (currentFID == 'x') {
            currentFID = DriveApp.getRootFolder().getId();
            curFN.setText('MyDrive/')
        };
        var startFolder1 = DriveApp.getFolderById(currentFID);
        while (folders.hasNext()) {
            var folder = startFolder1.next();
            Logger.log(folder.getName());
        };
        var startFolder = DriveApp.getFolders();
        while (startFolder.hasNext()) {
            var folder = folders.next();
            Logger.log(folder.getName());
        };
        listF.clear().addItem('No More Sub Folders!', 'x').addItem('Go back to Root', 'x');
        if (folders.length > 0) {
            listF.clear();
            listF.addItem('Select Sub Folder', 'x')
        };
        for (var i = 0; i < startFolder.length; i++) {
            listF.addItem(folders[i].getName(), folders[i].getId())
        }
        curFN.setText(currentFN + DriveApp.getFoldersById(currentFID).getName() + '/');
        if (currentFID == DriveApp.getRootFolder().getId()) {
            curFN.setText('MyDrive/')
        };
        curFID.setText(currentFID);
        return app;
    }
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
        name: "Export Row to Document",
        functionName: "getTemplates"
    }];
    ss.addMenu("Generate Documents Here!", menuEntries);
}

Here is what is happening: enter image description here

Upvotes: 0

Views: 93

Answers (1)

Sachin K
Sachin K

Reputation: 394

problem is when you opening spreadsheet ui you are not loading folder name into listbox listF. here what you did......

while (folders.hasNext())

{ var folder = folders.next(); Logger.log(folder.getName()); };

you need to add statement like this

listF.addItem(folder.getName(),folder.getId())

List box now populated try this code.

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*
Template Generator By: Andre Fecteau - [email protected]
Original Code From: [email protected] (Found in the template gallery by searching "Templates" It is the first One.
Major Help from: Serge Insas On Stack Overflow (He did most of the work.)
Link 1: http://stackoverflow.com/questions/18147798/e-undefined-google-script-error
Link 2: http://stackoverflow.com/questions/18132837/have-a-listbox-populate-with-every-folder-in-mydrive
How To Use:
First: each column is designated in your Template by {Column Letter} for example for column A it would be {A}
Second: You can change this, but your Template must be in a folder called "Templates." This folder can be anywhere in your drive.
Third: Click "Generate Documents Here!" Then click "Export Row to Document"
Fourth: Type in the row you want to export. Chose your Folder Path. Click Submit.
NOTE ON FOURTH STEP: If you want your number to skip the header row add a +1 to line 32.
This would mean if you typed "2" in the row box it actually exports row 3. I took this out because it can get confusing at times.
NOTE: Line 71 you can edit the word "Templates" to whatever folder you saved your Template into.
NOTE: Line 36 you can edit to change what comes up in the name of the file that is created. To do this just edit the column letter in the following piece: "+Sheet.getRange('E'+row).getValue()+"
You can then delete any other columns you do not want by deleteing the section of code that looks like the following: '+Sheet.getRange('D'+row).getValue()+'
Feel free to edit this code as you wish and for your needs. That is what I did with the original code.
So there is no reason I should restrict what others do with this code.

Bug Fix Log:
* changed row: 32 to remove the +1 6/18/2014
* changed row: 40 to remov e the row-- 6/18/2014
* changed row: 68 to update to DriveApp because of Google API Update - 4/21/15
* changed row: 68 to update to getFoldersByName and getFilesByType to update to new Google API - 4/21/15
*/
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function generateDocument(e) {
        var template = DriveApp.getFileById(e.parameter.Templates);
        Logger.log(template.getName());
        var Sheet = SpreadsheetApp.getActiveSpreadsheet();
        var row = Number(e.parameter.row) //+1; // Remove the // in this line next to the +1 to skip headers
        Logger.log(row);
        var currentFID = e.parameter.curFID;
        Logger.log(currentFID);
        var myDocID = template.makeCopy(Sheet.getRange('B' + row).getValue() + ' - ' + Sheet.getRange('E' + row).getValue() + ' - ' + Sheet.getRange('D' + row).getValue() + ' - ' + Sheet.getRange('X' + row).getValue()).getId();
        var myDoc = DocumentApp.openById(myDocID);
        var copyBody = myDoc.getActiveSection();
        var Sheet = SpreadsheetApp.getActiveSpreadsheet();
        //row--; // decrement row number to be in concordance with real row numbers in sheet
        var myRow = SpreadsheetApp.getActiveSpreadsheet().getRange(row + ":" + row);
        for (var i = 1; i < Sheet.getLastColumn() + 1; i++) {
            var myCell = myRow.getCell(1, i);
            copyBody.replaceText("{" + myCell.getA1Notation().replace(row, "") + "}", myCell.getValue());
        }
        myDoc.saveAndClose();
        var destFolder = DriveApp.getFolderById(currentFID);
        Logger.log(myDocID);
        var doc = DriveApp.getFileById(myDocID); // get the document again but using DriveApp this time...
        doc.addToFolder(destFolder); // add it to the desired folder
        doc.removeFromFolder(DriveApp.getRootFolder()); // I did it step by step to be more easy to follow
        var pdf = DriveApp.getFileById(myDocID).getAs("application/pdf");
        destFolder.createFile(pdf); // this will create the pdf file in your folder
        var app = UiApp.getActiveApplication();
        app.close();
        return app;
    }
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function getTemplates() {
        var doc = SpreadsheetApp.getActiveSpreadsheet();
        var app = UiApp.createApplication().setTitle('Generate from template');
        // Create a grid with 3 text boxes and corresponding labels
        var grid = app.createGrid(5, 2);
        grid.setWidget(0, 0, app.createLabel('Template name:'));
        var list = app.createListBox();
        list.setName('Templates');
        grid.setWidget(0, 1, list);

        var folders = DriveApp.getFoldersByName("Templates");
        while (folders.hasNext()) 
        {
          var folder = folders.next();
          Logger.log(folder.getName());
          var allMyFilesByType = folder.getFilesByType(MimeType.GOOGLE_DOCS)
        };
        while (allMyFilesByType.hasNext())
        {
          var file = allMyFilesByType.next();
          list.addItem(file.getName());
          Logger.log(file.getName());
        };

        grid.setWidget(1, 0, app.createLabel('Row:'));
        var row = app.createTextBox().setName('row');
        row.setValue(SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getRow());
        grid.setWidget(1, 1, row);
        var curFN = app.createTextBox().setText('MyDrive/').setName('curFN').setId('curFN').setWidth('400');
        var curFID = app.createTextBox().setText(DriveApp.getRootFolder().getId()).setName('curFID').setId('curFID').setVisible(false);
        var listF = app.createListBox().setName('listF').setId('listF').addItem('Please Select Folder', 'x');
        grid.setText(2, 0, 'Type Path:').setWidget(2, 1, curFN).setText(3, 0, 'OR').setText(4, 0, 'Choose Path:').setWidget(4, 1, listF).setWidget(3, 1, curFID);
        var folders = DriveApp.getRootFolder().getFolders();
        while (folders.hasNext()) {
            var folder = folders.next();
            listF.addItem(folder.getName(),folder.getId())
        };
        var handlerF = app.createServerHandler('folderSelect').addCallbackElement(grid);
        listF.addChangeHandler(handlerF);
        var panel = app.createVerticalPanel();
        panel.add(grid);
        var button = app.createButton('Submit');
        var handler = app.createServerClickHandler('generateDocument');
        handler.addCallbackElement(grid);
        button.addClickHandler(handler);
        // Add the button to the panel and the panel to the application, then display the application app in the Spreadsheet doc
        panel.add(button);
        app.add(panel);
        doc.show(app);
    }
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function folderSelect(e) 
{
        var app = UiApp.getActiveApplication();
        var currentFN = e.parameter.curFN;
        var currentFID = e.parameter.listF;
        Logger.log(currentFID);
        var listF = app.getElementById('listF');
        var curFN = app.getElementById('curFN');
        var curFID = app.getElementById('curFID');
        if (currentFID == 'x') {
            currentFID = DriveApp.getRootFolder().getId();
            curFN.setText('MyDrive/')
        };
        var startFolder = DriveApp.getFolderById(currentFID);
        var folders = startFolder.getFolders();
        listF.clear().addItem('No More Sub Folders!', 'x').addItem('Go back to Root', 'x');
        if (folders.length > 0) {
            listF.clear();
            listF.addItem('Select Sub Folder', 'x')
        };
        while (folders.hasNext()) {
            var folder = folders.next();
            listF.addItem(folder.getName(),folder.getId())
        };
        curFN.setText(currentFN + DriveApp.getFolderById(currentFID).getName() + '/');
        if (currentFID == DriveApp.getRootFolder().getId()) {
            curFN.setText('MyDrive/')
        };
        curFID.setText(currentFID);
        return app;
    }
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
        name: "Export Row to Document",
        functionName: "getTemplates"
    }];
    ss.addMenu("Generate Documents Here!", menuEntries);
}

Upvotes: 1

Related Questions