Add an Update button to the Dashboard for React Drive CMS

prepareSheets.js

// This script is intended for use with https://github.com/misterfresh/react-drive-cms
// Add it as a bound script to the Dashboard Sheet file
function onOpen() {
    var dashboard = SpreadsheetApp.getActiveSpreadsheet();
    var updateDashboard = [{name: "Update Dashboard", functionName: "prepareSheets"}];
    dashboard.addMenu("Update", updateDashboard);
}
function prepareSheets() {
    'use strict';
    var Preparator = {
        projectFolderName: 'React Drive CMS Demo',
        postsFolderName: 'Posts',
        dashboardSpreadsheetName: 'Dashboard',
        imagesFolderName: 'Images',
        run: function () {
            var self = this;
            self.setFiles();
            self.getPostsData();
            self.getImagesData();
            self.openSheets();
            self.prepareImagesRange();
            self.populateDashboard();
            self.hideInternal();
        },
        setFiles: function () {
            var self = this;
            self.folders = DriveApp.getFoldersByName(self.projectFolderName);
            self.projectFolder = self.folders.next();
            var projectFiles = self.projectFolder.getFiles();
            while (projectFiles.hasNext()) {
                var file = projectFiles.next();
                Logger.log(file.getName());
                switch (file.getName()) {
                    case self.dashboardSpreadsheetName:
                        self.dashboardFile = file;
                        break;
                    case self.categoriesSpreadsheetName:
                        self.categoriesFile = file;
                        break;
                }
            }
            var subFolders = self.projectFolder.getFolders();
            while (subFolders.hasNext()) {
                var folder = subFolders.next();
                Logger.log(folder.getName());
                switch (folder.getName()) {
                    case self.postsFolderName:
                        self.postsFolder = folder;
                        break;
                    case self.imagesFolderName:
                        self.imagesFolder = folder;
                        break;
                }
            }
        },
        getPostsData: function () {
            var self = this;
            self.postsData = [];
            var posts = self.postsFolder.getFiles();
            while (posts.hasNext()) {
                var post = posts.next();
                self.postsData.push(
                    {
                        id: post.getId(),
                        title: post.getName(),
                        lastUpdated: post.getLastUpdated()
                    }
                );
            }
            self.postCount = self.postsData.length;
        },
        openSheets: function () {
            var self = this;
            var dashboardSpreadsheet = SpreadsheetApp.open(self.dashboardFile);
            self.postsSheet = dashboardSpreadsheet.getSheets()[0];
            self.imagesSheet = dashboardSpreadsheet.getSheets()[1];
        },
        getImagesData: function () {
            var self = this;
            self.imagesData = [];
            var images = self.imagesFolder.getFiles();
            while (images.hasNext()) {
                var image = images.next();
                self.imagesData.push(
                    {
                        id: image.getId(),
                        title: image.getName()
                    }
                );
            }
        },
        prepareImagesRange: function () {
            var self = this;
            for (var i = 2; i <= self.imagesSheet.getLastRow(); i++) {
                var imageRow = self.imagesSheet.getRange(i, 1, 1, 4);
                // search for an image id match
                var noMatch = true;
                for (var k = 0; k < self.imagesData.length; k++) {
                    if (typeof self.imagesData[k] === 'undefined') {
                        continue;
                    }
                    var imageData = self.imagesData[k];
                    var imageId = imageRow.getCell(1, 3).getValue();
                    //if ids match, update the post name
                    if (imageId === imageData['id']) {
                        var imageName = self.imagesSheet.getRange(i, 1);
                        imageName.setValue(imageData['title']);
                        delete self.imagesData[k];
                        noMatch = false;
                        break;
                    }
                }
                // if no ids match, delete the row and decrement i
                if (noMatch) {
                    Logger.log('no image match for row ' + i);
                    self.imagesSheet.deleteRow(i);
                    i--;
                    continue;
                }
                var lastPostsRow = self.postsData.length + 1;
                self.postsImageNameRangeA1 = "D2:D" + lastPostsRow;
                var imageNameCell = imageRow.getCell(1, 1);
                var imageAvailableCell = imageRow.getCell(1, 2);
                imageAvailableCell.setFormula("=IF(COUNTIF(" + 'Posts!' + self.postsImageNameRangeA1 + ';' + imageNameCell.getA1Notation() + ")<1;1;0)");
                var availableNameCell = imageRow.getCell(1, 4);
                availableNameCell.setFormula("=IF(" + imageAvailableCell.getA1Notation() + ">0;" + imageNameCell.getA1Notation() + ";" + '""' + ")");
            }
            //remaining images are inserted in the sheet
            for (var j = 0; j < self.imagesData.length; j++) {
                if (typeof self.imagesData[j] === 'undefined') {
                    continue;
                }
                var imagesDat = self.imagesData[j];
                self.imagesSheet.appendRow([imagesDat['title'], 1, imagesDat['id']]);
            }

            var lastRow = self.imagesSheet.getLastRow();
            self.imageRangeA1 = "A2:C" + lastRow;
            self.imageRange = self.imagesSheet.getRange(self.imageRangeA1);
            self.imageRange.sort(1);
            self.imageNamesRange = self.imagesSheet.getRange(2, 1, lastRow);
            self.availableImagesRange = self.imagesSheet.getRange(2, 4, lastRow);
            var availableImagesRangeA1 = "D2:D" + lastRow;
            //only keep available image + used image to avoid validation error message
            for (var l = 0; l < self.postsData.length; l++) {
                var unionA1 = "E" + (( l + 2) + "");
                var unionCell = self.imagesSheet.getRange(unionA1).getCell(1, 1);
                var postImageA1 = "D" + ((l + 2) + "");
                unionCell.setFormula("=TRANSPOSE(UNIQUE({" + 'Images!' + availableImagesRangeA1 + '; Posts!' + postImageA1 + "}))");
            }
        },
        populateDashboard: function () {
            var self = this;
            for (var i = 2; i <= self.postsSheet.getLastRow(); i++) {
                var postRow = self.postsSheet.getRange(i, 1, 1, 7);

                // search for a post id match
                var noMatch = true;
                for (var k = 0; k < self.postsData.length; k++) {
                    if (typeof self.postsData[k] === 'undefined') {
                        continue;
                    }
                    var postData = self.postsData[k];
                    var postId = postRow.getCell(1, 5).getValue();
                    //if ids match, update the post name
                    if (postId === postData['id']) {
                        var postName = self.postsSheet.getRange(i, 1);
                        postName.setValue(postData['title']);
                        var postLastUpdated = self.postsSheet.getRange(i, 7);
                        postLastUpdated.setValue(postData['lastUpdated']);
                        delete self.postsData[k];
                        noMatch = false;
                        break;
                    }
                }
                // if no ids match, delete the row and decrement i
                if (noMatch) {
                    Logger.log('no match for row ' + i);
                    self.postsSheet.deleteRow(i);
                    i--;
                    continue;
                }
                //set image validation
                var imageCell = postRow.getCell(1, 4);
                var lastAvailableImageCell = self.imagesSheet.getRange(i, 5, 1, self.postCount).getCell(1, self.postCount);
                var ownImagesRangeA1 = 'Images!E' + ((i) + "") + ':' + lastAvailableImageCell.getA1Notation();
                Logger.log(ownImagesRangeA1);
                var ownImageRange = self.imagesSheet.getRange(ownImagesRangeA1);
                var imagesRule = SpreadsheetApp.newDataValidation().setAllowInvalid(true).requireValueInRange(ownImageRange).build();

                imageCell.setDataValidation(imagesRule);
                var imageIdCell = postRow.getCell(1, 6);
                imageIdCell.setFormula("=VLOOKUP(" + imageCell.getA1Notation() + ';' + 'Images!' + self.imageRangeA1 + ';' + 3 + ")");


            }
            //remaining posts are inserted in the sheet
            for (var j = 0; j < self.postsData.length; j++) {
                if (typeof self.postsData[j] === 'undefined') {
                    continue;
                }
                var postDat = self.postsData[j];
                self.postsSheet.appendRow([postDat['title'], '', '', '', postDat['id'], '', postDat['lastUpdated']]);
            }
        },
        hideInternal: function () {
            var self = this;
            self.postsSheet.hideColumns(5, 3);
            self.imagesSheet.hideSheet();
        }
    };
    Preparator.run();
}