CODEWITHSUNDEEP
google-apps-script
DanCue
DanCue

Reputation: 776

Grab URL for XLSX file in GDrive using Google Script

Background: I have an xlsx file that is sent to me via email on a weekly basis. This file is saved to my drive as it arrives using a trigger.

Request: I would now like to copy the data from that file into a google sheets dashboard. To start I'd like to know if there is a way to programatically grab the URL to that file in google script. The manual action would be to double click the file in drive and then click on "Open with Google Sheets" and then copy the URL. Obviously I do not want to have to do that manually each week. Can google script do this?

Upvotes: 0

Views: 85

Answers (1)

IMTheNachoMan
IMTheNachoMan

Reputation: 5811

Does this help? I've commented to help explain what it is doing.

You'll need to enable the Drive API from Resources > Advanced Google Services.

This will save the attachment to Drive and convert it to a Google Sheets. At that point you can open the converted Google Sheets file and pull data to save to another Google Sheets data.

function doIt()
{
    // get where we want to save the file
    var destinationFolder = DriveApp.getFolderById("...");

    // search for the email with the attachment you want to save
    var emailThreads = GmailApp.search("...");

    // go through each thread
    for(var i = 0, numThreads = emailThreads.length; i < numThreads; ++i)
    {
        // get all of the emails in the thread
        var emailMessages = emailThreads[i].getMessages();

        // get all of the messages
        for(var j = 0, numMessages = emailMessages.length; j < numMessages; ++j)
        {
            // get the message
            var emailMessage = emailMessages[j];

            // we only want messages that are not in the trash
            if(emailMessage.isInTrash()) continue;

            // get all of the attachments
            var emailAttachments = emailMessage.getAttachments({includeInlineImages: false, includeAttachments: true});

            // go through all attachments
            for(var k = 0, numAttachments = emailAttachments.length; k < numAttachments; ++k)
            {
                // get the attachment
                var emailAttachment = emailAttachments[k];

                // save the attachment to the destination folder
                var savedFile = destinationFolder.createFile(emailAttachment);

                // log the URL of the saved file
                Logger.log(savedFile.getUrl());

                // create a copy of the file as a Google Sheets file
                var sheetsFile = Drive.Files.insert({
                    "title": "...",
                    "parents": [{"id": destinationFolder.getId()}]
                }, savedFile.getBlob(), {
                    "convert": true,
                    "supportsAllDrives": true,
                    "supportsTeamDrives": true
                });

                // log the URL and file ID of the converted file
                Logger.log(sheetsFile.selfLink);
                Logger.log(sheetsFile.id);
            }
        }
    }
}

Upvotes: 1

Related Questions