Skip to content

Sheet Manager

Jump to bottom
Abdi Urgessa edited this page Apr 21, 2024 · 5 revisions

The SheetManager class is designed to facilitate interaction with Google Sheets, allowing a Telegram bot to perform various operations such as reading, writing, and manipulating data within a specified Google Sheet. This class leverages the Google Apps Script environment, ensuring seamless integration with Google's suite of services.

Usage Examples

Before utilizing the SheetManager, establish a connection with your Telegram bot and specify the Google Sheet ID as shown:

const telesun = new Telesun.config({
      telegram: '7172550832:AA*********Vw',
      spreadSheetId: "1CWOWH********kvhw",
    });

telesun.on("message", (ctx) => {
   ctx.sheet("Sheet1").appendRow(["Data1", "Data2", "Data3"]);
});

Important: Replace "YOUR_BOT_TOKEN" and "YOUR_SHEET_ID" with your actual bot token and Google Sheet ID, respectively. Ensure that "Sheet1" is replaced with the actual name of your sheet, which cannot be "____SHEET____" as it's a placeholder for demonstration purposes.

Sheet Operations

Appending Rows

  • appendRow(arrayOfValues): Adds a new row at the bottom of the sheet with the specified array of values.

Clearing Data

  • clear(): Clears all data from the sheet.
  • clearByRange(row, col, numCol, numRow): Clears a specific range within the sheet, specified by its starting row and column, along with the number of columns and rows to clear.
  • clearValue(row, col): Clears the content of a specific cell.

Finding Data

  • findOne(query): Uses a text finder to locate the next cell that matches the specified query.
  • findAll(query): Finds all cells that match the query and returns them.

Deleting Content

  • deleteColumn(colNum): Deletes the column at the specified column number.
  • deleteColumns(colIndex, howMany): Deletes a specified number of columns starting from the given column index.
  • deleteRow(rowNum): Deletes the row at the specified row number.
  • deleteRows(rowIndex, howMany): Deletes a specified number of rows starting from the given row index.

Sheet Data and Structure

  • getSheetData(): Returns all the data in the sheet as a 2D array.
  • getLastColumn(): Returns the number of the last column that has content.
  • getLastRow(): Returns the number of the last row that has content.

Inserting Columns and Rows

  • insertColumnAfter(indexCol): Inserts a new column directly after the specified column index.
  • insertColumnBefore(indexCol): Inserts a new column directly before the specified column index.
  • insertColumns(indexCol, howMany): Inserts a specified number of columns starting from the given column index.
  • insertRowAfter(afterPosition): Inserts a new row directly after the specified row position.
  • insertRowBefore(beforePosition): Inserts a new row directly before the specified row position.
  • insertRows(rowIndex, numRows): Inserts a specified number of rows starting from the given row index.

Moving Content

  • moveColumns(range, destinationIndex): Moves a column range to the specified destination index.
  • moveRows(range, destinationIndex): Moves a row range to the specified destination index.

Miscellaneous Operations

  • setName(name): Sets or changes the name of the sheet.
  • sortByColumn(columnPosition, ascending): Sorts the sheet based on the specified column. The sorting can be ascending (true) or descending (false).
  • saveOne(value, row, col): Saves a single value to the specified cell.
  • saveMany(arrayOfRows, row, col): Saves multiple values starting from the specified cell, useful for updating multiple cells at once.
  • getValue(row, col): Retrieves the value of a specific cell.
  • getValues(row, col, numRow, numCol): Retrieves a range of values from the specified starting cell, defined by the number of rows and columns to include.

Example Usage

For a practical example, consider appending a row with user input from a Telegram message:

telesun.on("message", (ctx) => {
   const messageText = ctx.update.message.text; // Get the text from the incoming message.
   ctx.sheet("YourSheetName").appendRow([messageText]);
});

Advanced Usage

Performing complex operations such as sorting, finding, or manipulating data can significantly enhance your bot's interaction with Google Sheets. For example, sorting data by a specific column:

ctx.sheet("Sheet1").sortByColumn(1, true);

Or finding and processing data within the sheet:

let foundCells = ctx.sheet("Sheet1").findAll("SearchQuery");
foundCells.forEach(cell => {
   // Process each found cell
});

Conclusion

The SheetManager class offers a robust and intuitive way to manage Google Sheets within a Telegram bot environment, especially tailored for Google Apps Script. Through its comprehensive set of operations, bots can seamlessly read, write, and manipulate sheet data, enhancing the bot's capabilities and interaction with users.

Clone this wiki locally