METRIC RAT AI2 - Working with Text and CSV Files on Google Drive

Working with Text and CSV Files on Google Drive

INTRO

This guide is intended to demonstrate how you can work directly with text and csv files, and the data therein, stored on your google drive, from your AI2 app. This provides some shortcuts to using google sheets as an intermediary, and also makes it easier to directly share the files with others or with other apps.

SETUP

To follow this guide you will need:

a folder/subfolders on google drive for your files
a google spreadsheet (for listing files and IDs). This is not essential because you could do this work just with google apps script, but it helps with visibility, and provides an out of app listing of all your current text and csv files
a google apps script web app, bound to the spreadsheet
an App Inventor app to interface with the web app and google drive

Folders

create a folder on your google drive, giving it a suitable name, for this demo I have used a folder called TextAndCSV
create two subfolders inside this folder called TXT and CSV
these folders can be public or private
get the IDs for the folders TextAndCSV, TXT, & CSV and save these for later

Spreadsheet

create a google sheet in your TextAndCSV folder, for this demo I named it TXTAndCSVOnGD
rename the first sheet to "assets"
You do not need to do anything else to the spreadsheet, the script will build the content later
get the ID for the spreadsheet TXTAndCSVOnGD, and save this for later
Keep the spreadsheet open for the next step

Google Apps Script

On the spreadsheet menu bar, select Tools then Script Editor
This will open a new script project bound to your spreadsheet
Name the project: TXTandCSVonGD

App Inventor

create a new project, called TextAndCsvFilesOnGD
you will definitely need a Web component, so drag that out from the Connectivity drawer onto your designer

WORKFLOW and FUNCTIONS

I will show separate functions for each of the actions required, these will be either called in or incorporated to the doGet(e) for the web app, which I will show later.

(click the arrow on the right to see the code)

Creating Files

Before we can do anything else, we are going to need some files, we will create three example .txt files and three example .csv files. By using the extension (.txt or .csv) for these files, Google Drive automatically sets the file/mimetype. It is possible to create several files with the same name on Google Drive (each would have a different file ID). It is best to use unique filenames to avoid confusion. (Note: it is possible to create and add content to a text/csv file at the same time in script, I have chosen to create empty files then add content in this demo)

function createEmptyFile(folderId, filename) {

var content,folder;

content = "";

folder = DriveApp.getFolderById(folderId);

folder.createFile(filename,content)

}

Create and Return the File Listings and Information

Now we have some files, we can return to the app all the associated information as displayed in the spreadsheet. The following function will update the spreadsheet contents with the latest file information and return all this data as a JSON list to the app. This data is needed to perform many of the functions. It is quite a long function, it will iterate over any sub folders in the main folder, and uses a sub function to help list the file type . It requires the base folder ID, the name of the base folder, and the name of the sheet in the spreadsheet. The spreadsheet ID is not required because we are using a script bound to the spreadsheet. This function should be run whenever there is a change to the file information (not needed if a content change)

function listbyIdFoldersAndSubFoldersContents(folderName,folderId,sheet) {

var ss = SpreadsheetApp.getActive();

var sheet = ss.getSheetByName(sheet);

sheet.clear();

sheet.appendRow( ['Folder','Folder ID','Filename', 'File Type', 'File ID', 'Download URL','Access'] );

var myFormat = sheet.getRange("A1:G1");

myFormat.setBackground("#d9ead3").setFontWeight("bold");

var myFolder = folderId;

var folder = DriveApp.getFolderById(myFolder);

traverseFolders(folder, folderName, folder.getName());

}

function traverseFolders(folder, folderName, path) {

var sheet = SpreadsheetApp.getActiveSheet();

if ( folder.getName() != folderName ) {

var files = folder.getFiles(), file, fileName;

while (files.hasNext())

{

file = files.next();

fileName = file.getName();

access = DriveApp.getFileById(file.getId()).getSharingAccess()

sheet.appendRow([folder.getName(), folder.getId(), fileName, niceFileType(file.getMimeType()), file.getId(), "https://docs.google.com/uc?export=download&id=" +file.getId(), access]);

}

var folders = folder.getFolders(), childFolder;

while (folders.hasNext())

{

childFolder = folders.next();

traverseFolders(childFolder, path + ", " + childFolder.getName());

}

function niceFileType( mimeType ) {

if (typeof this.fileType === 'undefined') {

this.fileType = {};

this.fileType[MimeType.FOLDER] = "Folder";

this.fileType[MimeType.GOOGLE_APPS_SCRIPT] = "Google Apps Script";

this.fileType[MimeType.GOOGLE_DOCS] = "Google Doc";

this.fileType[MimeType.GOOGLE_DRAWINGS] = "Google Drawing";

this.fileType[MimeType.GOOGLE_FORMS] = "Google Form";

this.fileType[MimeType.GOOGLE_SHEETS] = "Google Sheet";

this.fileType[MimeType.GOOGLE_SLIDES] = "Google Slides";

this.fileType[MimeType.JPEG] = "jpg";

this.fileType[MimeType.PNG] = "png";

this.fileType[MimeType.BMP] = "bmp";

this.fileType[MimeType.GIF] = "gif";

this.fileType[MimeType.SVG] = "svg";

this.fileType[MimeType.PDF] = "pdf";

this.fileType[MimeType.CSV] = "csv";

this.fileType[MimeType.PLAIN_TEXT] = "txt";

this.fileType[MimeType.HTML] = "html";

}

return (this.fileType.hasOwnProperty(mimeType)) ? this.fileType[mimeType] : "Other";

}

Append Data To Files

Here we need to send the holding folder ID, the fileName of the file, and the content for the file. A new line is created for each append (unless the file is empty). This can be used the plain text files or for csv files to add additional rows of data.

function AppendToFile(folderId, fileName, content) {

var folder = DriveApp.getFolderById(folderId);

var fileList = folder.getFilesByName(fileName);

if (fileList.hasNext()) {

var file = fileList.next();

var currentContent = file.getBlob().getDataAsString();

if (currentContent.length != 0) {

var combinedContent = currentContent + '\n' + content;

} else {

var combinedContent = content;

}

file.setContent(combinedContent);

}

Delete Files

We may also want to delete files. The good news here is that when deleted from the google drive folder, the file is sent to the Bin, and will remain there for 30 days, then to be fully deleted. Such files can be restored manually from the Bin in Google Drive. The holding folder ID and the fileName are required.

function DeleteFileByName(folderId,filename) {

var folder = DriveApp.getFolderById(folderId);

var files = folder.getFilesByName(filename);

while (files.hasNext()) {

files.next().setTrashed(true);

}

Setting the Availability of Files

We may want to share some of our files with others, even those without a google account, or we may want to keep a file private, only available to the owner of the google account. The ID for the file is required. You can see the permissions changes in google drive, and in the spreadsheet (Access column) which will show 'PRIVATE' or 'ANYONE'. We can set the file permissions to Public or Private using these functions:

function setPermsPublic(fileID) {

setFile = DriveApp.getFileById(fileID);

setFile.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);

}

function setPermsPrivate(fileID) {

setFile = DriveApp.getFileById(fileID);

setFile.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.EDIT);

}

Get File Content

When we want to return the content of a file to the app we would use this function. It requires the file ID, and returns the content as a String.

function getFileContent(fileId) {

var file = DriveApp.getFileById(fileId);

var docContent = file.getBlob().getDataAsString();

}

Overwrite or Update Content

We will want to either overwrite all the content, or update existing content edited in the app. This function is similar to the Append function.

function OverwriteFile(folderId, fileName, content) {

var folder = DriveApp.getFolderById(folderId);

var fileList = folder.getFilesByName(fileName);

if (fileList.hasNext()) {

var file = fileList.next();

file.setContent(content);

}

doGet(e)

We will now generate a google apps script web app to action all these functions, called from App Inventor. Add this code to your script project, overwriting anything that is already there. Then publish the script as "me" and "Anyone" and give permissions as requested for the script to access your google drive. (See here for a full guide on creating and publishing a google apps script web app) Once published you will be provided with the script url, copy this for use in the blocks.

function doGet(e) {

var message = '';

if (e.parameter.FN == "Create") {

createEmptyFile(e.parameter.folderId, e.parameter.filename)

message = e.parameter.filename + " created";

}

else if (e.parameter.FN == "Delete") {

DeleteFileByName(e.parameter.folderId,e.parameter.filename)

message = e.parameter.filename + " deleted (it can be found in the Google Drive Bin)";

}

else if (e.parameter.FN == "SetPublic") {

setPermsPublic(e.parameter.fileId);

var ufile = DriveApp.getFileById(e.parameter.fileId);

message = ufile.getName() + " set to Anyone";

}

else if (e.parameter.FN == "SetPrivate") {

setPermsPrivate(e.parameter.fileId);

var rfile = DriveApp.getFileById(e.parameter.fileId);

message = rfile.getName() + " set to Private";

}

else if (e.parameter.FN == "Fetch") {

listbyIdFoldersAndSubFoldersContents(e.parameter.foldername,e.parameter.folderId,e.parameter.sheet)

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheetByName(e.parameter.sheet);

var rg = sh.getDataRange().getDisplayValues();

var data = JSON.stringify(rg);

message = data;

}

else if ((e.parameter.FN == "GetContent") || (e.parameter.FN == "Update")) {

var gfile = DriveApp.getFileById(e.parameter.fileId);

var docContent = gfile.getBlob().getDataAsString();

message = docContent;

}

else if (e.parameter.FN == "Append") {

AppendToFile(e.parameter.folderId, e.parameter.filename, e.parameter.content);

message = "Content appended to " + e.parameter.filename;

}

else if ((e.parameter.FN == "Overwrite") || (e.parameter.FN == "SetUpdate")) {

OverwriteFile(e.parameter.folderId, e.parameter.filename, e.parameter.content);

if (e.parameter.FN == "Overwrite") {

message = "Content overwritten for " + e.parameter.filename;

} else {

message = "Content updated for " + e.parameter.filename;

}

return ContentService.createTextOutput(message);

} //end of doGet()

function createEmptyFile(folderId, filename) {

var content,folder;

content = "";

folder = DriveApp.getFolderById(folderId);

folder.createFile(filename,content)

}

function listbyIdFoldersAndSubFoldersContents(folderName,folderId,sheet) {

var ss = SpreadsheetApp.getActive();

var sheet = ss.getSheetByName(sheet);

sheet.clear();

sheet.appendRow( ['Folder','Folder ID','Filename', 'File Type', 'File ID', 'Download URL','Access'] );

var myFormat = sheet.getRange("A1:G1");

myFormat.setBackground("#d9ead3").setFontWeight("bold");

var myFolder = folderId;

var folder = DriveApp.getFolderById(myFolder);

traverseFolders(folder, folderName, folder.getName());

}

function traverseFolders(folder, folderName, path) {

var sheet = SpreadsheetApp.getActiveSheet();

if ( folder.getName() != folderName ) {

var files = folder.getFiles(), file, fileName;

while (files.hasNext())

{

file = files.next();

fileName = file.getName();

access = DriveApp.getFileById(file.getId()).getSharingAccess()

sheet.appendRow([folder.getName(), folder.getId(), fileName, niceFileType(file.getMimeType()), file.getId(), "https://docs.google.com/uc?export=download&id=" +file.getId(), access]);

}

var folders = folder.getFolders(), childFolder;

while (folders.hasNext())

{

childFolder = folders.next();

traverseFolders(childFolder, path + ", " + childFolder.getName());

}

function niceFileType( mimeType ) {

if (typeof this.fileType === 'undefined') {

this.fileType = {};

this.fileType[MimeType.FOLDER] = "Folder";

this.fileType[MimeType.GOOGLE_APPS_SCRIPT] = "Google Apps Script";

this.fileType[MimeType.GOOGLE_DOCS] = "Google Doc";

this.fileType[MimeType.GOOGLE_DRAWINGS] = "Google Drawing";

this.fileType[MimeType.GOOGLE_FORMS] = "Google Form";

this.fileType[MimeType.GOOGLE_SHEETS] = "Google Sheet";

this.fileType[MimeType.GOOGLE_SLIDES] = "Google Slides";

this.fileType[MimeType.JPEG] = "jpg";

this.fileType[MimeType.PNG] = "png";

this.fileType[MimeType.BMP] = "bmp";

this.fileType[MimeType.GIF] = "gif";

this.fileType[MimeType.SVG] = "svg";

this.fileType[MimeType.PDF] = "pdf";

this.fileType[MimeType.CSV] = "csv";

this.fileType[MimeType.PLAIN_TEXT] = "txt";

this.fileType[MimeType.HTML] = "html";

}

return (this.fileType.hasOwnProperty(mimeType)) ? this.fileType[mimeType] : "Other";

}

function DeleteFileByName(folderId,filename) {

var folder = DriveApp.getFolderById(folderId);

var files = folder.getFilesByName(filename);

while (files.hasNext()) {

files.next().setTrashed(true);

}

function setPermsPrivate(fileID) {

setFile = DriveApp.getFileById(fileID);

setFile.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.EDIT);

}

function setPermsPublic(fileID) {

setFile = DriveApp.getFileById(fileID);

setFile.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);

}

function AppendToFile(folderId, fileName, content) {

var folder = DriveApp.getFolderById(folderId);

var fileList = folder.getFilesByName(fileName);

if (fileList.hasNext()) {

var file = fileList.next();

var currentContent = file.getBlob().getDataAsString();

if (currentContent.length != 0) {

var combinedContent = currentContent + '\n' + content;

} else {

var combinedContent = content;

}

file.setContent(combinedContent);

}

function OverwriteFile(folderId, fileName, content) {

var folder = DriveApp.getFolderById(folderId);

var fileList = folder.getFilesByName(fileName);

if (fileList.hasNext()) {

var file = fileList.next();

file.setContent(content);

}

function getFileContent(fileId) {

var file = DriveApp.getFileById(fileId);

var docContent = file.getBlob().getDataAsString();

}

BLOCKS

Variables

The most important variable is <action>, which is used to identify where we are and what we are doing in the app, and tells the web app what to do.

Buttons

When the app starts, we call the getFilesList (which also has its own button). This ensures we have the file information required for the various functions. Most buttons will do much the same thing: set the action, build the file information list, open the spinner to select a file, which, after file selection, then calls the web component to perform the function. Append, Overwrite, and Update all make use of the Append textbox to display/edit content. The AppendSubmit button makes a direct call to the web component.

Spinner

The spinner is used to provide a list of files available on Google Drive, then generates a web component call based upon the action selected.

Web

We use two web components, Web2 is for the downloading of a file to the ASD, Web1 does the main grunt of the work, handling all the action requests to the web app, and returning the content or message as required.

DataList as seen on the spreadsheet

VIDEO

AIA and FILES

TextAndCSVToGoogleDrive_Blank.aia

To use the above aia project file, you will need to setup folders on your google drive: TXTAndCSV then TXT and CSV as a subfolders, then create a spreadsheet in the TXTAndCSV folder (I called mine TextAndCsvOnGD) and set up a bound apps script project from the spreadsheet, Add the script code above to the script project, then publish as a web app, getting the script url. In the aia project you will need to add the script url and the folder names and IDs.

Also, an example working with tinyDB

Page updated

Google Sites

Report abuse