import type { Spreadsheet , Sheet } from 'gasmask/src/SpreadsheetApp' ; export type { Spreadsheet , Sheet } from 'gasmask/src/SpreadsheetApp' ; param { Spreadsheet } activeSpreadsheet return { SheetQueryBuilder } export function sheetQuery ( activeSpreadsheet ? ) { return new SheetQueryBuilder ( activeSpreadsheet ) ; } export type DictObject = { [ key ] : any ; } ; export type RowObject = { [ key ] : any ; __meta : { row : number ; cols : number ; } ; } ; export type WhereFn = ( row : RowObject ) => boolean ; export type UpdateFn = ( row : RowObject ) => RowObject | undefined ; declare var SpreadsheetApp ; /**
 * SheetQueryBuilder class - Kind of an ORM for Google Sheets
 */ export class SheetQueryBuilder { activeSpreadsheet : any ; columnNames : string [ ] = [ ] ; sheetName : string | undefined ; headingRow : number = 1 ; whereFn : WhereFn | undefined ; _sheet : any ; _sheetValues : any ; _sheetHeadings : string [ ] = [ ] ; constructor ( activeSpreadsheet ? ) { this . activeSpreadsheet = activeSpreadsheet || SpreadsheetApp . getActiveSpreadsheet ( ) ; } select ( columnNames ) { this . columnNames = Array . isArray ( columnNames ) ? columnNames : [ columnNames ] ; return this ; } param { string } sheetName param { number } headingRow return { SheetQueryBuilder } from ( sheetName , headingRow = 1 ) { this . sheetName = sheetName ; this . headingRow = headingRow ; return this ; } param { Function } fn return { SheetQueryBuilder } where ( fn ) { this . whereFn = fn ; return this ; } return { SheetQueryBuilder } deleteRows ( ) { const rows = this . getRows ( ) ; let i = 0 ; rows . forEach ( ( row ) => { const deleteRowRange = this . _sheet . getRange ( row . __meta . row - i , 1 , 1 , row . __meta . cols ) ; deleteRowRange . deleteCells ( SpreadsheetApp . Dimension . ROWS ) ; i ++ ; } ) ; this . clearCache ( ) ; return this ; } param { UpdateFn } updateFn return { SheetQueryBuilder } updateRows ( updateFn ) { const rows = this . getRows ( ) ; rows . forEach ( ( row ) => { const updateRowRange = this . _sheet . getRange ( row . __meta . row , 1 , 1 , row . __meta . cols ) ; const updatedRow = updateFn ( row ) ; let arrayValues = [ ] ; if ( updatedRow && updatedRow . __meta ) { delete updatedRow . __meta ; arrayValues = Object . values ( updatedRow ) ; } else { delete row . __meta ; arrayValues = Object . values ( row ) ; } updateRowRange . setValues ( [ arrayValues ] ) ; } ) ; this . clearCache ( ) ; return this ; } return { Sheet } getSheet ( ) { if ( ! this . _sheet ) { this . _sheet = this . activeSpreadsheet . getSheetByName ( this . sheetName ) ; } return this . _sheet ; } /**
     * Get values in sheet from current query + where condition
     */ getValues ( ) { if ( ! this . _sheetValues ) { const zh = this . headingRow - 1 ; const sheet = this . getSheet ( ) ; const rowValues = [ ] ; const allValues = sheet . getDataRange ( ) . getValues ( ) ; const sheetValues = allValues . slice ( 1 + zh ) ; const numCols = sheetValues [ 0 ] . length ; const numRows = sheetValues . length ; const headings = ( this . _sheetHeadings = allValues [ zh ] || [ ] ) ; for ( let r = 0 ; r < numRows ; r ++ ) { const obj = { __meta : { row : r + ( this . headingRow + 1 ) , cols : numCols } } ; for ( let c = 0 ; c < numCols ; c ++ ) { obj [ headings [ c ] ] = sheetValues [ r ] [ c ] ; } rowValues . push ( obj ) ; } this . _sheetValues = rowValues ; } return this . _sheetValues ; } return { RowObject [ ] } getRows ( ) { const sheetValues = this . getValues ( ) ; return this . whereFn ? sheetValues . filter ( this . whereFn ) : sheetValues ; } return { string [ ] } getHeadings ( ) { if ( ! this . _sheetHeadings || ! this . _sheetHeadings . length ) { const zh = this . headingRow - 1 ; const sheet = this . getSheet ( ) ; const numCols = sheet . getLastColumn ( ) ; this . _sheetHeadings = sheet . getSheetValues ( 1 , 1 , this . headingRow , numCols ) [ zh ] ; } return this . _sheetHeadings || [ ] ; } param { DictObject [ ] } newRows return { SheetQueryBuilder } insertRows ( newRows ) { const sheet = this . getSheet ( ) ; const headings = this . getHeadings ( ) ; newRows . forEach ( ( row ) => { if ( ! row ) { return ; } const rowValues = headings . map ( ( heading ) => { return heading && row [ heading ] ? row [ heading ] : '' ; } ) ; sheet . appendRow ( rowValues ) ; } ) ; return this ; } return { SheetQueryBuilder } clearCache ( ) { this . _sheetValues = null ; this . _sheetHeadings = [ ] ; SpreadsheetApp . flush ( ) ; return this ; } }