groton-school/blackbaud-to-google-lists

Google Workspace Add-on to export/sync Blackbaud lists to Google Sheets

blackbaud-to-google-lists's Issues

multipage updates are failing

https://github.com/groton-school/bb-lists-to-google-sheets/blob/b46cb3702d5de1dc140cb185996ee8a9e4e6127f/src/Workflow/ImportData.ts#L61

            frame.shift();
        }
        data.push(...frame);
        // FIXME multipage updates are failing
        if (frame.length >= SKY.PAGE_SIZE) {
            progress.setStatus(`Loaded page ${page} (${data.length - 1} rows)`);
            progress.setValue(page++);

data and page should be stored in SKY.ServiceManaager, not State

https://github.com/groton-school/bb-lists-to-google-sheets/blob/78518888a6de10bc0e54682f476dbed7f8c93436/src/State.ts#L19

    UpdateExisting = 'update',
}

const FOLDER = `${PREFIX}.State.folder`;
const SPREADSHEET = `${PREFIX}.State.spreadsheet`;
const SHEET = `${PREFIX}.State.sheet`;
const SELECTION = `${PREFIX}.State.selection`;
const LIST = `${PREFIX}.State.list`;
const INTENT = `${PREFIX}.State.intent`;

// FIXME data and page should be stored in SKY.ServiceManaager, not State
const DATA = `${PREFIX}.State.data`;
const PAGE = `${PREFIX}.State.page`;

export function getFolder() {
    const id = Terse.PropertiesService.getUserProperty(FOLDER);
    let folder = null;
    if (id) {
        folder = DriveApp.getFolderById(id);
    }
    return folder;
}

export function setFolder(folder: GoogleAppsScript.Drive.Folder) {
    const id = folder && folder.getId();
    return Terse.PropertiesService.setUserProperty(FOLDER, id);
}

export function getSpreadsheet(): GoogleAppsScript.Spreadsheet.Spreadsheet {
    const id = Terse.PropertiesService.getUserProperty(SPREADSHEET);
    let spreadsheet = null;
    if (id) {
        spreadsheet = SpreadsheetApp.openById(id);
    } else {
        spreadsheet = SpreadsheetApp.getActive();
        setSpreadsheet(spreadsheet);
    }
    return spreadsheet;
}

export function setSpreadsheet(
    spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet
) {
    const id = spreadsheet && spreadsheet.getId();
    return Terse.PropertiesService.setUserProperty(SPREADSHEET, id);
}

export function getSheet(): GoogleAppsScript.Spreadsheet.Sheet {
    const spreadsheet = getSpreadsheet();
    let sheet = null;
    if (spreadsheet) {
        const name = Terse.PropertiesService.getUserProperty(SHEET);
        if (name) {
            sheet = spreadsheet.getSheetByName(name);
        } else {
            sheet = spreadsheet.getActiveSheet();
            setSheet(sheet);
        }
    }
    return sheet;
}

export function setSheet(sheet: GoogleAppsScript.Spreadsheet.Sheet) {
    const name = sheet && sheet.getName();
    setSpreadsheet(sheet && sheet.getParent());
    return Terse.PropertiesService.setUserProperty(SHEET, name);
}

export function getSelection(): GoogleAppsScript.Spreadsheet.Range {
    const sheet = getSheet();
    let selection = null;
    if (sheet) {
        const range = Terse.PropertiesService.getUserProperty(SELECTION);
        if (range) {
            selection = sheet.getRange(range);
        } else {
            selection = sheet.getActiveRange();
            setSelection(selection);
        }
    }
    return selection;
}

export function setSelection(range: GoogleAppsScript.Spreadsheet.Range) {
    const a1notation = range && range.getA1Notation();
    setSheet(range && range.getSheet());
    return Terse.PropertiesService.setUserProperty(SELECTION, a1notation);
}

export const getList = Terse.PropertiesService.getUserProperty.bind(
    null,
    LIST
) as () => SKY.School.Lists.Metadata;
export const setList = Terse.PropertiesService.setUserProperty.bind(null, LIST);
export const getData = Terse.PropertiesService.getUserProperty.bind(null, DATA);
export const setData = Terse.PropertiesService.setUserProperty.bind(null, DATA);

export function appendData(page) {
    const data = getData() || [];
    data.push(...page);
    setData(data);
}

export const getPage = Terse.PropertiesService.getUserProperty.bind(
    null,
    PAGE
) as () => number;
export const setPage = Terse.PropertiesService.setUserProperty.bind(null, PAGE);
export const getIntent = Terse.PropertiesService.getUserProperty.bind(
    null,
    INTENT
) as () => Intent;
export const setIntent = Terse.PropertiesService.setUserProperty.bind(
    null,
    INTENT
);

export function reset() {
    Terse.PropertiesService.deleteUserProperty(FOLDER);
    Terse.PropertiesService.deleteUserProperty(SPREADSHEET);
    Terse.PropertiesService.deleteUserProperty(SHEET);
    Terse.PropertiesService.deleteUserProperty(SELECTION);
    Terse.PropertiesService.deleteUserProperty(LIST);
    Terse.PropertiesService.deleteUserProperty(DATA);
    Terse.PropertiesService.deleteUserProperty(PAGE);
    setIntent(Intent.CreateSpreadsheet);
}

export function update(arg) {
    if (arg) {
        var {
            parameters: { state },
        } = arg;
        if (state) {
            state = JSON.parse(state);
            if (state.folder) {
                setFolder(DriveApp.getFolderById(state.folder));
            }

            var spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet;
            if (state.spreadsheet) {
                spreadsheet = SpreadsheetApp.openById(state.spreadsheet);
            }
            var sheet: GoogleAppsScript.Spreadsheet.Sheet;
            if (state.sheet) {
                spreadsheet = spreadsheet || getSpreadsheet();
                sheet = spreadsheet.getSheetByName(state.sheet);
            }
            if (state.selection) {
                sheet = sheet || getSheet();
                setSelection(sheet.getRange(state.selection));
            } else if (sheet) {
                setSheet(sheet);
            } else if (spreadsheet) {
                setSpreadsheet(spreadsheet);
            }
            if (state.list) {
                setList(state.list);
            }
            if (state.page) {
                setPage(state.page);
            }
            if (state.data) {
                setData(state.data);
            }
            if (state.intent) {
                setIntent(state.intent);
            }
        }
    }
}

export function toString(): string {
    const folder = getFolder();
    const intent = getIntent();
    const spreadsheet = getSpreadsheet();
    const sheet = getSheet();
    const selection = getSelection();
    const list = getList();
    const page = getPage();
    const data = getData();
    return JSON.stringify(
        {
            folder: folder && folder.getId(),
            intent,
            spreadsheet: spreadsheet && spreadsheet.getId(),
            sheet: sheet && sheet.getName(),
            selection: selection && selection.getA1Notation(),
            list,
            page,
            data,
        },
        null,
        2
    );
}

reduce unnecessary rehydration -- only do so on demand, as-needed

https://github.com/groton-school/bb-lists-to-google-sheets/blob/a9e67f5d3b2337d7950aef8cba061b0ed93cf97e/State.js#L12

  intent: Intent.CreateSpreadsheet,

  reset: (serializedState = null) => {
    // TODO reduce unnecessary rehydration -- only do so on demand, as-needed
    const previousState = serializedState && JSON.parse(serializedState);

    if (previousState && previousState.spreadsheet) {

Change only the cells that are updated

This would allow the Sheets version history to, well… be useful. (Might also be a painfully slow way to run the update?)

why isn't the appended sheet made active?

https://github.com/groton-school/bb-lists-to-google-sheets/blob/7832f15b76140eb6cc7ff0afee7b416422c291e0/src/Actions/Lists/InsertData.ts#L106

import Lists from '../../Lists';
import Sheets from '../../Sheets';
import State, { Intent } from '../../State';
import { sheetAppendedAction } from '../Sheets/SheetAppended';
import { spreadsheetCreatedAction } from '../Sheets/SpreadsheetCreated';
import { updatedAction } from '../Sheets/Updated';
import EmptyList from './EmptyList';

export function insertDataAction(arg = null) {
    State.update(arg);
    const data = State.getData();

    if (!data || data.length == 0) {
        return EmptyList;
    }

    var range = null;
    switch (State.getIntent()) {
        case Intent.AppendSheet:
            State.setSheet(State.getSpreadsheet().insertSheet());
            range = Sheets.adjustRange(
                {
                    row: 1,
                    column: 1,
                    numRows: data.length,
                    numColumns: data[0].length,
                },
                null,
                State.getSheet()
            );
            break;
        case Intent.ReplaceSelection:
            State.getSelection().clearContent();
            range = Sheets.adjustRange(
                {
                    row: State.getSelection().getRow(),
                    column: State.getSelection().getColumn(),
                    numRows: data.length,
                    numColumns: data[0].length,
                },
                State.getSelection()
            );
            break;
        case Intent.UpdateExisting:
            const metaRange = Sheets.metadata.get(Sheets.metadata.RANGE);
            range = Sheets.adjustRange(
                {
                    ...metaRange,
                    numRows: data.length,
                    numColumns: data[0].length,
                },
                Sheets.rangeFromJSON(metaRange)
            );
            break;
        case Intent.CreateSpreadsheet:
        default:
            State.setSpreadsheet(
                SpreadsheetApp.create(State.getList().name, data.length, data[0].length)
            );
            if (State.getFolder()) {
                DriveApp.getFileById(State.getSpreadsheet().getId()).moveTo(
                    State.getFolder()
                );
            }
            State.setSheet(State.getSpreadsheet().getSheets()[0]);
            range = State.getSheet().getRange(1, 1, data.length, data[0].length);
    }

    range.setValues(data);
    range.offset(0, 0, 1, range.getNumColumns()).setFontWeight('bold');
    const timestamp = new Date().toLocaleString();

    Sheets.metadata.set(Sheets.metadata.LIST, State.getList(), range.getSheet());
    Sheets.metadata.set(
        Sheets.metadata.RANGE,
        Sheets.rangeToJSON(range),
        range.getSheet()
    );
    Sheets.metadata.set(
        Sheets.metadata.LAST_UPDATED,
        timestamp,
        range.getSheet()
    );
    range
        .offset(0, 0, 1, 1)
        .setNote(`Last updated from "${State.getList().name}" ${timestamp}`);

    switch (State.getIntent()) {
        case Intent.ReplaceSelection:
            Sheets.metadata.set(
                Sheets.metadata.NAME,
                `${range.getSheet().getName()}-existing`,
                range.getSheet()
            );
            return updatedAction();
        case Intent.UpdateExisting:
            if (
                range.getSheet().getName() == Sheets.metadata.get(Sheets.metadata.NAME)
            ) {
                Lists.setSheetName(range.getSheet());
            }
            return updatedAction();
        case Intent.AppendSheet:
            range.getSheet().setFrozenRows(1);
            Lists.setSheetName(range.getSheet(), timestamp);
            // TODO why isn't the appended sheet made active?
            State.getSpreadsheet().setActiveSheet(range.getSheet());
            return sheetAppendedAction();
        case Intent.CreateSpreadsheet:
        default:
            range.getSheet().setFrozenRows(1);
            Lists.setSheetName(range.getSheet(), timestamp);
            return spreadsheetCreatedAction();
    }
}
global.action_lists_insertData = insertDataAction;
const InsertData = 'action_lists_insertData';
export default InsertData;

Opening link not working

Related to #1 probably

Order of operations doesn't seem to matter (OpenLink before Navigation or vice versa)

https://github.com/groton-school/bb-lists-to-google-sheets/blob/605518af4fcddb9f07b417cd9d20dfa8a5e4a9b5/Common.js#L256

    .build();
}


function actionHome() {
  var url = null;
  var card = null;
  if (arguments && arguments.length > 0) {
    url = arguments[0].url;
    card = arguments[0].card;
  }

  var action = CardService.newActionResponseBuilder()
    .setNavigation(CardService.newNavigation()
      .popToRoot()
      .updateCard(card || launch()));

  /* 
   * FIXME Opening link not working
   *   Related to #1 probably
   *   Order of operations doesn't seem to matter (OpenLink before Navigation or vice versa)
   */
  if (url) {
    action = action.setOpenLink(CardService.newOpenLink()
      .setUrl(url));
  }
  return action.build();
}

Insert into selection (expanding area as needed)

https://github.com/groton-school/bb-lists-to-google-sheets/blob/1daedc97faaa927884ab6542f6d8968f42742db0/Spreadsheets.js#L18

          .setFunctionName('actionUpdateSheet')
          .setParameters({ state: State.toJSON() }))));
  }
  // TODO Insert into selection (expanding area as needed)
  return card
    .addSection(CardService.newCardSection()
      .addWidget(CardService.newTextButton()

range is not being captured by this

https://github.com/groton-school/bb-lists-to-google-sheets/blob/f98a6ea72bb9d905b379dc8e301153863284915a/src/State.ts#L168

        const spreadsheet = SpreadsheetApp.getActive();
        if (spreadsheet) {
            // FIXME range is not being captured by this
            State.setSelection(
                spreadsheet.getActiveSheet().getSelection().getActiveRange()
            );

Add "one time import" workflow

Related to #37, have a workflow that does not leave a connection available (to potentially scramble your data). A one-time import that gets the data and moves on with its life.

Write while pulling updates?

At the moment, a large list could conceivably run out of memory as it is pulled entirely out of Blackbaud, stored into memory, and then written to the spreadsheet.

It would probably be safer (?) to write each page of data as it is read, ensuring that:

1. There is a constant single page buffer of data in memory at any time
2. A partially-run update would still write the data as far as it has loaded
3. Potentially faster writes, as both the write to the spreadsheet and the read from Blackbaud could be asynchronous, rather than in series.

insert logic to detect change of sheet on update

https://github.com/groton-school/bb-lists-to-google-sheets/blob/afda435ec3e6d4fba3f9c23f41539d5871aac6b1/Spreadsheets.js#L36

function actionUpdateSheet({ parameters: { state } }) {
  State.restore(state);
  // FIXME insert logic to detect change of sheet on update
  const data = SKY.school.v1.lists(State.metadata.list.id, SKY.Response.Array);
  const updatedRange = [State.metadata.range[0], State.metadata.range[1], data.length, data[0].length];
  if (data.length > State.metadata.range[2]) {

once again not creating in desired folder

https://github.com/groton-school/bb-lists-to-google-sheets/blob/f98a6ea72bb9d905b379dc8e301153863284915a/src/Actions/Lists/InsertData.ts#L60

            State.setSpreadsheet(
                SpreadsheetApp.create(State.getList().name, data.length, data[0].length)
            );
            // FIXME once again not creating in desired folder
            if (State.getFolder()) {
                DriveApp.getFileById(State.getSpreadsheet().getId()).moveTo(
                    State.getFolder()

everything from <br/> onward is being escaped (probably by the HtmlService)

https://github.com/groton-school/bb-lists-to-google-sheets/blob/01239f48d817b897dcdad9108e98f44a8fe3199e/src/Workflow/ImportData.ts#L140

            range.getSheet().setFrozenRows(1);
            range.getSheet().setName(list.name);
            message = g.SpreadsheetApp.Dialog.getHtml({
                // FIXME everything from <br/> onward is being escaped (probably by the HtmlService)
                message: `"${list.name
                    }" on Blackbaud has been connected to the sheet of the same name in the spreadsheet "${range
                        .getSheet()

why isn't the appended sheet made active?

https://github.com/groton-school/bb-lists-to-google-sheets/blob/4c0825f2e02f1b4b089a41b159e435c5131c2a69/Lists.js#L163

            data[0].length
          );
          if (State.folder) {
            DriveApp.getFileById(State.spreadsheet.getId()).moveTo(
              State.folder
            );
          }
          State.sheet = State.spreadsheet.getSheets()[0];
          range = State.sheet.getRange(
            1,
            1,
            data.length,
            data[0].length
          );
      }

      range.setValues(data);
      range.offset(0, 0, 1, range.getNumColumns()).setFontWeight('bold');

      Sheets.metadata.set(
        Sheets.metadata.LIST,
        State.list,
        range.getSheet()
      );
      Sheets.metadata.set(
        Sheets.metadata.RANGE,
        Sheets.rangeToJSON(range),
        range.getSheet()
      );

      switch (State.intent) {
        case Intent.ReplaceSelection:
          Sheets.metadata.set(
            Sheets.metadata.NAME,
            `${range.getSheet().getName()}-existing`,
            range.getSheet()
          );
          return TerseCardService.replaceStack(
            Sheets.cards.updated()
          );
        case Intent.UpdateExisting:
          if (
            range.getSheet().getName() ==
            Sheets.metadata.get(Sheets.metadata.NAME)
          ) {
            Lists.setSheetName(range.getSheet());
          }
          return TerseCardService.replaceStack(
            Sheets.cards.updated()
          );
        case Intent.AppendSheet:
          range.getSheet().setFrozenRows(1);
          Lists.setSheetName(range.getSheet());
          // TODO why isn't the appended sheet made active?
          State.spreadsheet.setActiveSheet(range.getSheet());
          return TerseCardService.replaceStack(
            Sheets.cards.sheetAppended()
          );
        case Intent.CreateSpreadsheet:
        default:
          range.getSheet().setFrozenRows(1);
          Lists.setSheetName(range.getSheet());
          return TerseCardService.replaceStack(
            Sheets.cards.spreadsheetCreated()
          );
      }
    },

    emptyList(data) {
      return TerseCardService.replaceStack(Lists.cards.emptyList(data));
    },
  },

  cards: {

...and we're back to not moving the spreadsheet to the current folder, again

https://github.com/groton-school/bb-lists-to-google-sheets/blob/17091c3a6bfca1ea189fc2e3b38742de128c98de/Lists.js#L114

            data.length,
            data[0].length
          );
          // FIXME ...and we're back to not moving the spreadsheet to the current folder, again
          if (State.folder) {
            DriveApp.getFileById(State.spreadsheet.getId()).moveTo(
              State.folder

Target.Selection needs its own dialog

https://github.com/groton-school/bb-lists-to-google-sheets/blob/01239f48d817b897dcdad9108e98f44a8fe3199e/src/Workflow/ImportData.ts#L117

        .offset(0, 0, 1, 1)
        .setNote(`Last updated from "${list.name}" ${timestamp.toLocaleString()}`);

    let message = 'Complete'; // FIXME Target.Selection needs its own dialog
    switch (target) {
        case Target.Sheet:
            range.getSheet().setFrozenRows(1);

bundle SKY as a separate module for reuse?

https://github.com/groton-school/bb-lists-to-google-sheets/blob/78518888a6de10bc0e54682f476dbed7f8c93436/src/SKY/index.ts#L6

export * as School from './School';
export * as ServiceManager from './ServiceManager';

export const PAGE_SIZE = 1000;

// TODO bundle SKY as a separate module for reuse?

implement editor add-on to detect when active tab changes

https://github.com/groton-school/bb-lists-to-google-sheets/blob/8628e803bc6f728e8291a2ba97ec86b8113f48cd/Sheets.js#L1

// TODO implement editor add-on to detect when active tab changes
const Sheets = {
  META: {
    LIST: `${App.PREFIX}.list`,
    RANGE: `${App.PREFIX}.range`,
    NAME: `${App.PREFIX}.name`
  },

  rangeToJSON(range) {
    return {
      row: range.getRow(),
      column: range.getColumn(),
      numRows: range.getNumRows(),
      numColumns: range.getNumColumns(),
      sheet: range.getSheet().getName()
    };
  },

  rangeFromJSON(json) {
    return State.spreadsheet
      .getSheetByName(json.sheet)
      .getRange(json.row, json.column, json.numRows, json.numColumns);
  },

  adjustRange({ row, column, numRows, numColumns }, range = null, sheet = null) {
    if (range) {
      sheet = range.getSheet();
      if (numRows > range.getNumRows()) {
        sheet.insertRows(range.getLastRow() + 1, numRows - range.getNumRows());
      }
      if (numColumns > range.getNumColumns()) {
        sheet.insertColumns(range.getLastColumn() + 1, numColumns - range.getNumColumns());
      }
    } else if (sheet) {
      if (numRows < sheet.getMaxRows()) {
        sheet.deleteRows(numRows + 1, sheet.getMaxRows() - numRows);
      }
      if (numColumns < sheet.getMaxColumns()) {
        sheet.deleteColumns(numColumns + 1, sheet.getMaxColumns() - numColumns);
      }
    }
    return sheet.getRange(row, column, numRows, numColumns);
  },

  actions: {
    spreadsheetCreated({ parameters: { state } }) {
      State.restore(state);
      const url = State.spreadsheet.getUrl(); // App launch will reset the State
      return TerseCardService.replaceStack(App.launch(), url);
    },

    breakConnection({ parameters: { state } }) {
      State.restore(state);
      return TerseCardService.pushCard(Sheets.cards.confirmBreakConnection());
    },

    deleteMetadata({ parameters: { state } }) {

should this be resilient to sheet name changes?

https://github.com/groton-school/bb-lists-to-google-sheets/blob/647c55c73bc400111159c0dce88e9ce1db780181/src/Metadata.ts#L35

        return null;
    }
    const sheet = SpreadsheetApp.getActive().getSheetByName(json.sheet);
    // TODO should this be resilient to sheet name changes?
    return sheet.getRange(json.row, json.column, json.numRows, json.numColumns);
}

const get = (key: string, sheet?: GoogleAppsScript.Spreadsheet.Sheet) => {
    sheet = sheet || SpreadsheetApp.getActive().getActiveSheet();
    return g.SpreadsheetApp.DeveloperMetadata.get(sheet, key);
};

export const getList = (
    sheet?: GoogleAppsScript.Spreadsheet.Sheet
): SKY.School.Lists.Metadata => get(LIST, sheet);
export const getRange = (sheet?: GoogleAppsScript.Spreadsheet.Sheet) =>
    rangeFromJSON(get(RANGE, sheet));
export const getLastUpdated = (sheet?: GoogleAppsScript.Spreadsheet.Sheet) =>
    get(LAST_UPDATED, sheet);

const set = (
    key: string,
    sheet: GoogleAppsScript.Spreadsheet.Sheet = null,
    value: any
) => {
    sheet = sheet || SpreadsheetApp.getActive().getActiveSheet();
    return g.SpreadsheetApp.DeveloperMetadata.set(sheet, key, value);
};

export const setList = (
    list: SKY.School.Lists.Metadata,
    sheet?: GoogleAppsScript.Spreadsheet.Sheet
) => set(LIST, sheet, list);
export const setRange = (
    range: GoogleAppsScript.Spreadsheet.Range,
    sheet?: GoogleAppsScript.Spreadsheet.Sheet
) => set(RANGE, sheet, rangeToJSON(range));
export const setLastUpdated = (
    lastUpdated: Date,
    sheet?: GoogleAppsScript.Spreadsheet.Sheet
) => set(LAST_UPDATED, sheet, lastUpdated.toLocaleString());

const remove = (key: string, sheet?: GoogleAppsScript.Spreadsheet.Sheet) => {
    sheet = sheet || SpreadsheetApp.getActive().getActiveSheet();
    return g.SpreadsheetApp.DeveloperMetadata.remove(sheet, key);
};

export const removeList = remove.bind(null, LIST);
export const removeRange = remove.bind(null, RANGE);

can also infer folder from parent of current spreadsheet

https://github.com/groton-school/bb-lists-to-google-sheets/blob/8628e803bc6f728e8291a2ba97ec86b8113f48cd/App.js#L11

const App = {
  PREFIX: 'org.groton.BbListsToGoogleSheets',
  LOGO_URL: 'https://drive.google.com/uc?id\u003d1sany_QufWim04ZXwj1cMWh03E-cYHWk0',

  launch(event) {
    if (event) {
      App.launchEvent = event;
    }
    State.reset();
    State.folder = Drive.inferFolder(App.launchEvent);
    // TODO can also infer folder from parent of current spreadsheet
    return App.cards.home();
  },

  actions: {
    home({ parameters: { state } }) {
      State.reset(state);
      return TerseCardService.replaceStack(App.cards.home());
    }
  },

  cards: {
    home() {
      if (State.sheet) {
        return Sheets.cards.options();
      } else {
        return Lists.cards.lists();
      }
    },

    error(message = "An error occurred") {
      return CardService.newCardBuilder()
        .setHeader(TerseCardService.newCardHeader(message))
        .addSection(CardService.newCardSection()
          .addWidget(TerseCardService.newDecoratedText('State', JSON.stringify(JSON.parse(State.toJSON()), null, 2)))
          .addWidget(TerseCardService.newTextButton('Start Over', '__App_actions_home')))
        .build();
    }
  }
}

function __App_launch(e) {
  return App.launch(e);
}

Document best practices

e.g. "Don't add columns to a sheet and then pull an update from Blackbaud unless you're sure the data in the added column is accurately keyed to an identifier from Blackbaud."

add a user-visible comment noting last update timestamp

https://github.com/groton-school/bb-lists-to-google-sheets/blob/eb6be4238ee041df22fa075949b10cf24e9d0a9f/Lists.js#L132

      range.setValues(data);
      range.offset(0, 0, 1, range.getNumColumns()).setFontWeight('bold');

      // TODO add a user-visible comment noting last update timestamp
      Sheets.metadata.set(
        Sheets.metadata.LIST,
        State.list,

insert logic to detect change of sheet on new spreadsheet

https://github.com/groton-school/bb-lists-to-google-sheets/blob/afda435ec3e6d4fba3f9c23f41539d5871aac6b1/Spreadsheets.js#L81

function actionNewSpreadsheet({ parameters: { state } }) {
  State.restore(state);
  // FIXME insert logic to detect change of sheet on new spreadsheet
  State.intent = Intent.CreateSpreadsheet;
  return CardService.newActionResponseBuilder()
    .setNavigation(CardService.newNavigation()

merge with Lists.actions.insertData() logic

https://github.com/groton-school/bb-lists-to-google-sheets/blob/6e803996cf3bc101b599f719793f1c69107005d6/Sheets.js#L43

const Sheets = {
  META: {
    LIST: 'org.groton.BbListsToGoogleSheets.list',
    RANGE: 'org.groton.BbListsToGoogleSheets.range',
    NAME: 'org.groton.BbListsToGoogleSheets.name'
  },

  actions: {
    replaceSelection({ parameters: { state } }) {
      State.restore(state);
      State.selection = State.sheet.getSelection().getActiveRange();
      State.intent = Intent.ReplaceSelection;

      return CardService.newActionResponseBuilder()
        .setNavigation(CardService.newNavigation()
          .pushCard(Lists.cards.lists()))
        .build();
    },

    appendSheet({ parameters: { state } }) {
      State.restore(state);
      // FIXME insert logic to detect change of sheet on append sheet
      State.intent = Intent.AppendSheet;
      return CardService.newActionResponseBuilder()
        .setNavigation(CardService.newNavigation()
          .pushCard(Lists.cards.lists()))
        .build();
    },

    newSpreadsheet({ parameters: { state } }) {
      State.restore(state);
      // FIXME insert logic to detect change of sheet on new spreadsheet
      State.intent = Intent.CreateSpreadsheet;
      return CardService.newActionResponseBuilder()
        .setNavigation(CardService.newNavigation()
          .pushCard(Lists.cards.lists()))
        .build();
    },

    update({ parameters: { state } }) {
      State.restore(state);
      // FIXME insert logic to detect change of sheet on update
      // FIXME merge with Lists.actions.insertData() logic
      const data = SKY.school.v1.lists(State.metadata.list.id, SKY.Response.Array);
      const updatedRange = [State.metadata.range[0], State.metadata.range[1], data.length, data[0].length];
      if (data.length > State.metadata.range[2]) {
        State.sheet.insertRows(State.metadata.range[0] + State.metadata.range[2], data.length - State.metadata.range[2]);
      }
      if (data[0].length > State.metadata.range[3]) {
        State.sheet.insertColumns(State.metadata.range[1] + State.metadata.range[3], data[0].length - State.metadata.range[3]);
      }
      State.sheet.getRange(...State.metadata.range).clearContent();
      State.sheet.getRange(...updatedRange).setValues(data);

      if (State.sheet.getName() == State.metadata.name) {
        State.sheet.setName(`${State.metadata.list.name} (${new Date().toLocaleString()})`);
        State.sheet.addDeveloperMetadata(Sheets.META.NAME, JSON.stringify(State.sheet.getName()));
      }

      return CardService.newActionResponseBuilder()
        .setNavigation(CardService.newNavigation()
          .pushCard(Sheets.cards.updated()))
        .build();
    },

    spreadsheetCreated({ parameters: { state } }) {
      State.restore(state);
      return App.actions.home({ url: State.spreadsheet.getUrl() })
    },

    breakConnection({ parameters: { state } }) {
      State.restore(state);
      return CardService.newActionResponseBuilder()
        .setNavigation(CardService.newNavigation()
          .pushCard(Sheets.cards.confirmBreakConnection()))
        .build();
    },

    deleteMetadata({ parameters: { state } }) {
      State.restore(state);
      for (const meta of State.sheet.getDeveloperMetadata()) {
        switch (meta.getKey()) {
          case Sheets.META.LIST:
          case Sheets.META.NAME:
          case Sheets.META.RANGE:
            meta.remove();
        }
      }
      return App.actions.home();
    }
  },

  cards: {
    options() {
      const card = CardService.newCardBuilder()
        .setHeader(CardService.newCardHeader()
          .setTitle(`${State.spreadsheet.getName()} Options`));

      if (State.metadata && State.metadata.list) {
        card.addSection(CardService.newCardSection()
          .addWidget(CardService.newDecoratedText()
            .setTopLabel(State.sheet.getName())
            .setText(`Update the data in the current sheet with the current "${State.metadata.list.name}" data from Blackbaud.`)
            .setWrapText(true))
          .addWidget(CardService.newTextParagraph()
            .setText('If the updated data contains more rows or columns than the current data, rows and/or columns will be added to the right and bottom of the current data to make room for the updated data without overwriting other information on the sheet. If the updated data contains fewer rows or columns than the current data, all non-overwritten rows and/or columns in the current data will be cleared of data.'))
          .addWidget(CardService.newTextButton()
            .setText('Update')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__Sheets_actions_update')
              .setParameters({ state: State.toJSON() })))
          .addWidget(CardService.newTextButton()
            .setText('Break Connection')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__Sheets_actions_breakConnection')
              .setParameters({ state: State.toJSON() }))));
      } else {
        card.addSection(CardService.newCardSection()
          .addWidget(CardService.newDecoratedText()
            .setTopLabel(State.sheet.getName())
            .setText(`Replace the currently selected cells (${State.sheet.getSelection().getActiveRange().getA1Notation()}) in the sheet "${State.sheet.getName()}" with data from Blackbaud`)
            .setWrapText(true))
          .addWidget(CardService.newTextButton()
            .setText('Replace Selection')
            .setOnClickAction(CardService.newAction()
              .setMethodName('__Sheets_actions_replaceSelection')
              .setParameters({ state: State.toJSON() }))));
      }

      return card
        .addSection(CardService.newCardSection()
          .addWidget(CardService.newTextButton()
            .setText('Append New Sheet')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__Sheets_actions_appendSheet')
              .setParameters({ state: State.toJSON() })))
          .addWidget(CardService.newTextButton()
            .setText('New Spreadsheet')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__Sheets_actions_newSpreadsheet')
              .setParameters({ state: State.toJSON() }))))
        .build();
    },

    spreadsheetCreated() {
      return CardService.newCardBuilder()
        .setHeader(CardService.newCardHeader()
          .setTitle(State.spreadsheet.getName()))
        .addSection(CardService.newCardSection()
          .addWidget(CardService.newTextParagraph()
            .setText(`The spreadsheet "${State.spreadsheet.getName()}"" has been created in the folder "${State.folder.getName()}" and populated with the data in "${State.list.name}" from Blackbaud.`))
          .addWidget(CardService.newTextButton()
            .setText('Open Spreadsheet')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__Sheets_actions_newSpreadsheet')
              .setParameters({ state: State.toJSON() }))))
        .build();
    },

    updated() {
      if (arguments && arguments.length > 0 && arguments[0].state) {
        State.restore(arguments[0].state);
      }
      return CardService.newCardBuilder()
        .setHeader(CardService.newCardHeader()
          .setTitle(`${State.sheet.getName()} Updated`))
        .addSection(CardService.newCardSection()
          .addWidget(CardService.newTextParagraph()
            .setText(`The sheet "${State.sheet.getName()}" of "${State.spreadsheet.getName()}" has been updated with the current data from "${State.metadata.list.name}" in Blackbaud.`))
          .addWidget(CardService.newTextButton()
            .setText('Done')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__App_actions_home'))))
        .build();
    },

    confirmBreakConnection() {
      return CardService.newCardBuilder()
        .setHeader(CardService.newCardHeader()
          .setTitle(`Are you sure?`))
        .addSection(CardService.newCardSection()
          .addWidget(CardService.newTextParagraph()
            .setText(`You are about to remove the developer metadata that connects this sheet to its Blackbaud data source. You will no longer be able to update the data on this sheet directly from Blackbaud. You will need to select the existing data and replace it with a new import from Blackbaud if you need to get new data.`))
          .addWidget(CardService.newTextButton()
            .setText('Delete Metadata')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__Sheets_actions_deleteMetadata')
              .setParameters({ state: State.toJSON() })))
          .addWidget(CardService.newTextButton()
            .setText('Cancel')
            .setOnClickAction(CardService.newAction()
              .setFunctionName('__App_actions_home'))))
        .build();
    }
  }
}

function __Sheets_actions_replaceSelection(...args) {
  return Sheets.actions.replaceSelection(...args);
}

function __Sheets_actions_appendSheet(...args) {
  return Sheets.actions.appendSheet(...args);
}

function __Sheets_actions_newSpreadsheet(...args) {
  return Sheets.actions.newSpreadsheet(...args);
}

function __Sheets_actions_update(...args) {
  return Sheets.actions.update(...args);
}

function __Sheets_actions_spreadsheetCreated(...args) {
  return Sheets.actions.spreadsheetCreated(...args);
}

function __Sheets_actions_breakConnection(...args) {
  return Sheets.actions.breakConnection(...args);
}

function __Sheets_actions_deleteMetadata(...args) {
  return Sheets.actions.deleteMetadata(...args);
}

g.Globals.register();

https://github.com/groton-school/bb-lists-to-google-sheets/blob/e7b8997e70f7fd08f7462ffe25e5e7843c792458/src/index.ts#L7

global.onOpen = AddOn.onOpen;
global.onInstall = AddOn.onInstall;

// TODO g.Globals.register();
global.include = g.HtmlService.include;
global.getProgress = g.HtmlService.Element.Progress.getProgress;
global.dialogClose = g.UI.Dialog.dialogClose;

going out of range on student enrollments

https://github.com/groton-school/bb-lists-to-google-sheets/blob/647c55c73bc400111159c0dce88e9ce1db780181/src/Workflow/ImportData.ts#L124

            frame.shift();
        }
        data.push(...frame);
        if (frame.length >= SKY.PAGE_SIZE) {
            progress.setStatus(`Loaded page ${page++} (${data.length - 1} rows)`);
            progress.incrementValue();
            progress.setMax(progress.getMax() + 1);
        } else {
            complete = true;
        }
    } while (!complete);
}

function connectToSpreadsheet(numRows: number, numColumns: number) {
    progress.setStatus('Connecting to spreadsheet…');
    progress.incrementValue();
    spreadsheet = SpreadsheetApp.getActive();
    switch (target) {
        case Target.Update:
        case Target.Selection:
            sheet = spreadsheet.getActiveSheet();
            if (target == Target.Update) {
                prevRange = Metadata.getRange(sheet);
            } else {
                prevRange = sheet.getActiveRange();
            }
            prevRange.clearContent();
            range = adjustRange(
                {
                    row: prevRange.getRow(),
                    column: prevRange.getColumn(),
                    numRows,
                    numColumns,
                },
                prevRange
            );
            break;
        case Target.Sheet:
            sheet = spreadsheet.insertSheet();
            range = adjustRange(
                {
                    row: 1,
                    column: 1,
                    numRows,
                    numColumns,
                },
                null,
                sheet
            );
            break;
        case Target.Spreadsheet:
            spreadsheet = SpreadsheetApp.create(list.name, numRows, numColumns);
            sheet = spreadsheet.getSheets()[0];
            range = sheet.getRange(1, 1, numRows, numColumns);
    }

    return { target, list, spreadsheet, sheet, range, prevRange };
}

// FIXME going out of range on student enrollments
function adjustRange(
    { row, column, numRows, numColumns },
    range = null,

update list when new sheet activates

https://github.com/groton-school/bb-lists-to-google-sheets/blob/a678ebb773f24a862cd8827dcfe90133457749bf/src/AddOn.ts#L3

import * as WorkFlow from './Workflow';

// FIXME update list when new sheet activates
export function onOpen() {
    let menu = SpreadsheetApp.getUi()
        .createAddonMenu()
        .addItem('Connect…', WorkFlow.Connect.getFunctionName())
        .addItem(`Update…`, WorkFlow.Update.getFunctionName())
        .addItem(`Disconnect…`, WorkFlow.Disconnect.getFunctionName())
        .addToUi();
}

Why doesn't the spreadsheet move to the folder?

https://github.com/groton-school/bb-lists-to-google-sheets/blob/afda435ec3e6d4fba3f9c23f41539d5871aac6b1/Common.js#L105

      dimensions.rows,
      dimensions.columns
    );

    if (State.folder) {
      // TODO Why doesn't the spreadsheet move to the folder?
      DriveApp.getFileById(State.spreadsheet.getId()).moveTo(State.folder);
    }

    State.sheet = State.spreadsheet.getSheets()[0];

  }

  const range = [1, 1, dimensions.rows, dimensions.columns];

need to deal with pagination

https://github.com/groton-school/bb-lists-to-google-sheets/blob/55b8354bb5a8c53880baa4032a7bc916ae23ebbe/SKY.js#L20

    v1: {
      lists: (list_id = null, format = SKY.Response.JSON) => {
        if (list_id) {
          // FIXME need to deal with pagination
          const response = SKY.call(`https://api.sky.blackbaud.com/school/v1/lists/advanced/${list_id}`);
          switch (format) {
            case SKY.Response.JSON:

detect if sheet or selection is already connected

Route into Update or limit target options

https://github.com/groton-school/bb-lists-to-google-sheets/blob/a678ebb773f24a862cd8827dcfe90133457749bf/src/Workflow/Connect.ts#L26

export const getFunctionName = () => 'connect';
global.connect = () => {
    /**
     * FIXME detect if sheet or selection is already connected
     *   Route into Update or limit target options
     */
    SpreadsheetApp.getUi().showModalDialog(
        g.HtmlService.createTemplateFromFile('templates/connect', {
            thread: Utilities.getUuid(),
        }),
        'Connect'
    );
};

logo is not loading

https://github.com/groton-school/bb-lists-to-google-sheets/blob/1320a5a2bdc8ce5e5a3a51b73977c6057a119285/src/SKY.ts#L187

                        .addWidget(
                            CardService.newGrid().addItem(
                                CardService.newGridItem().setImage(
                                    // FIXME logo is not loading
                                    CardService.newImageComponent().setImageUrl(LOGO_URL)
                                )
                            )

compile modules

https://github.com/groton-school/bb-lists-to-google-sheets/blob/315a0af0059969baa1cf56904dd4171bf62e31ae/webpack.config.js#L22

const path = require('path');
const GasPlugin = require('gas-webpack-plugin');

module.exports = {
  mode: 'development',
  devtool: 'inline-source-map',
  entry: {
    main: './src/index.ts',
  },
  output: {
    path: path.resolve(__dirname, './build'),
    filename: '[name]-bundle.js',
  },
  resolve: {
    extensions: ['.ts', '.js'],
  },
  module: {
    rules: [
      {
        test: /\.ts$/,
        loader: 'ts-loader',
        options: { allowTsInNodeModules: true }, // TODO compile modules
      },
    ],
  },
  plugins: [new GasPlugin()],
};

this fallback is unsafe without tracking tab changes!

https://github.com/groton-school/bb-lists-to-google-sheets/blob/78518888a6de10bc0e54682f476dbed7f8c93436/src/Sheets/index.ts#L16

import * as State from '../State';

export * as Metadata from './Metadata';

export function rangeToJSON(range) {
    return {
        row: range.getRow(),
        column: range.getColumn(),
        numRows: range.getNumRows(),
        numColumns: range.getNumColumns(),
        sheet: range.getSheet().getName(),
    };
}

export function rangeFromJSON(json) {
    // FIXME this fallback is unsafe without tracking tab changes!
    const sheet =
        State.getSpreadsheet().getSheetByName(json.sheet) || State.getSheet();
    return sheet.getRange(json.row, json.column, json.numRows, json.numColumns);
}

export function adjustRange(
    { row, column, numRows, numColumns },
    range = null,
    sheet = null
) {
    if (range) {
        sheet = range.getSheet();
        if (numRows > range.getNumRows()) {
            sheet.insertRows(range.getLastRow() + 1, numRows - range.getNumRows());
        }
        if (numColumns > range.getNumColumns()) {
            sheet.insertColumns(
                range.getLastColumn() + 1,
                numColumns - range.getNumColumns()
            );
        }
    } else if (sheet) {
        if (numRows < sheet.getMaxRows()) {
            sheet.deleteRows(numRows + 1, sheet.getMaxRows() - numRows);
        }
        if (numColumns < sheet.getMaxColumns()) {
            sheet.deleteColumns(numColumns + 1, sheet.getMaxColumns() - numColumns);
        }
    }
    return sheet.getRange(row, column, numRows, numColumns);
}

can also infer folder from parent of current spreadsheet

https://github.com/groton-school/bb-lists-to-google-sheets/blob/7832f15b76140eb6cc7ff0afee7b416422c291e0/src/index.ts#L11

import { homeCard } from './Actions/App/Home';
import Drive from './Drive';
import State from './State';

global.launch = (event) => {
    const folder = State.getFolder();
    State.reset();
    if (event) {
        State.setFolder(Drive.inferFolder(event));
    }
    // TODO can also infer folder from parent of current spreadsheet
    return homeCard();
};

insert logic to detect change of sheet on append sheet

https://github.com/groton-school/bb-lists-to-google-sheets/blob/afda435ec3e6d4fba3f9c23f41539d5871aac6b1/Spreadsheets.js#L71

function actionAppendNewSheet({ parameters: { state } }) {
  State.restore(state);
  // FIXME insert logic to detect change of sheet on append sheet
  State.intent = Intent.AppendSheet;
  return CardService.newActionResponseBuilder()
    .setNavigation(CardService.newNavigation()

this fallback is unsafe without tracking tab changes!

https://github.com/groton-school/bb-lists-to-google-sheets/blob/4c0825f2e02f1b4b089a41b159e435c5131c2a69/Sheets.js#L63

      column: range.getColumn(),
      numRows: range.getNumRows(),
      numColumns: range.getNumColumns(),
      sheet: range.getSheet().getName(),
    };
  },

  rangeFromJSON(json) {
    // FIXME this fallback is unsafe without tracking tab changes!
    const sheet =
      State.spreadsheet.getSheetByName(json.sheet) || State.sheet;
    return sheet.getRange(
      json.row,
      json.column,
      json.numRows,
      json.numColumns
    );
  },

  adjustRange(
    { row, column, numRows, numColumns },
    range = null,
    sheet = null
  ) {
    if (range) {
      sheet = range.getSheet();
      if (numRows > range.getNumRows()) {
        sheet.insertRows(
          range.getLastRow() + 1,
          numRows - range.getNumRows()
        );
      }
      if (numColumns > range.getNumColumns()) {
        sheet.insertColumns(
          range.getLastColumn() + 1,
          numColumns - range.getNumColumns()
        );
      }
    } else if (sheet) {
      if (numRows < sheet.getMaxRows()) {
        sheet.deleteRows(numRows + 1, sheet.getMaxRows() - numRows);
      }
      if (numColumns < sheet.getMaxColumns()) {
        sheet.deleteColumns(
          numColumns + 1,
          sheet.getMaxColumns() - numColumns
        );
      }
    }
    return sheet.getRange(row, column, numRows, numColumns);

data is not being written to selection

https://github.com/groton-school/bb-lists-to-google-sheets/blob/b46cb3702d5de1dc140cb185996ee8a9e4e6127f/src/Workflow/ImportData.ts#L90

            );
            break;
        case Target.selection:
            // FIXME data is not being written to selection
            sheet = spreadsheet.getActiveSheet();
            range = sheet.getActiveRange();
            range.clearContent();

Detect and alert when SKY API fails

This seems like a not infrequent occurrence.

Exception: Address unavailable: https://api.sky.blackbaud.com/school/v1/lists/advanced/XXXXXX?page=1
    at t.makeRequest(build/main-bundle:1:30065)
    at [unknown function](build/main-bundle:1:26636)
    at t.get(build/main-bundle:1:27091)
    at [unknown function](build/main-bundle:1:36761)
    at _(build/main-bundle:1:36982)

better UI on API authorization

https://github.com/groton-school/bb-lists-to-google-sheets/blob/7832f15b76140eb6cc7ff0afee7b416422c291e0/src/SKY.ts#L212

    public static callbackAuthorization(callbackRequest) {
        const authorized = SKY.getService().handleCallback(callbackRequest);
        // TODO better UI on API authorization
        if (authorized) {
            return HtmlService.createHtmlOutput('<h1>Authorized</h1>');
        } else {

detect if current sheet is actually updatable

Redirect into Connect workflow if not updateable

https://github.com/groton-school/bb-lists-to-google-sheets/blob/a678ebb773f24a862cd8827dcfe90133457749bf/src/Workflow/Update.ts#L8

export const getFunctionName = () => 'update';
global.update = () => {
    /*
     * FIXME detect if current sheet is actually updatable
     *   Redirect into Connect workflow if not updateable
     */
    const thread = Utilities.getUuid();
    SpreadsheetApp.getUi().showModalDialog(
        g.HtmlService.Element.Progress.getHtmlOutput(thread),