Google Sheets

Access and manipulate data in your Google Sheets straight from SAMMI!
For instance, you can set up your own leaderboard and easily share it with others.

Features

Available Commands

• Get Data, Find Data
• Append Data, Update Data, Batch Update, Copy Paste, Clear Data
• Auto Fill, Auto Sort, Randomize, Find and Replace, Delete Duplicates, Split Text
• Merge Cells, Unmerge Cells, Insert Range, Delete Range
• Create Spreadsheet, Duplicate Sheet, Add Sheet

Setup

• The extension only works with public spreadsheets created specifically with it. See how to make a spreadsheet public.
• Your read quota is 60 requests per minute, and it's exclusive to you (not shared with other users). There is no daily limit.
• Your write quota is 30 requests per minute (you can send batch requests that count as one), but it is shared with other extension users.
• The total quota is 300 requests per minute. The extension assumes no more than 10 users making 30 write requests per minute at the same time, though this might change in the future. No daily limit applies.

How It Works

The extension uses Google Sheets API endpoints to read and write data.
Reading data only requires an API key, while writing data is more complex. The extension uses a custom API to retrieve your refresh token, as this can only be done server-side and cannot be handled by your Bridge.

Initial Setup

1. Install the extension. You can follow the Extension Install Guide.

2. Configure INIT Settings button
   You should see a new premade deck in your SAMMI. Right-click on the red INIT Settings button - edit commands.

   • Refresh Token - allows the extension to access your Google Sheets and retrieve the refresh token. Please use this link to authorize the extension, and follow the instructions after you get redirected.

     This only grants access to files created with the extension, with no permissions for other files.

     If you get the 'Something went wrong' error, try opening the link in a different browser or incognito window. Firefox has been known to have issues at times.

   • API Key - you will need your own Google API Key.
     Follow the video guide showing the steps below:

     1. Go to the Google Cloud Console.
     2. Select or create a project for the API key.
     3. In the menu, choose APIs & Services.
     4. Enable the Google Sheets and Google Drive APIs.
     5. Create an API key under APIs & Services > Credentials.
     6. Optionally restrict the key to the Google Sheets API.
     7. Copy the key and paste it into the apiKey field of the INIT button.

   • Itch Key - this key was provided on the Itch.io purchase page. Retrieve it via the purchase confirmation email. For issues, join our Discord server for assistance.

   • Show Alerts - choose whether to show yellow notifications for successful requests (only errors are shown if set to false).

   • Configure Google Sheets OPTIONS commands. Select which Google Sheets commands to display in your command list to avoid clutter.
     Default commands: Get Data, Append Data, Update Data, Batch Update, Find Data, Sort Range.

3. Use the Create Spreadsheet button to generate your first spreadsheet. The extension can only modify spreadsheets it created.

   • After creating the spreadsheet, go to Google Sheets and manually make it public so the extension can read it.
   • (Optional) Return to the INIT Settings button and set the spreadsheetID if you plan to use a single spreadsheet. The ID can be found in the URL: https://docs.google.com/spreadsheets/d/YOURSPREADSHEETID/edit?usp=sharing.

4. Setup is now complete, and you can start using the extension!

General Parameters

Many buttons share the same parameters, so they will be omitted in the Buttons section.

SpreadsheetID

You can either enter it manually for each button or set the spreadsheetID variable in your INIT button if you're using a single Spreadsheet.

The Spreadsheet ID can be found by going to your spreadsheet in the browser and copying it from the URL: https://docs.google.com/spreadsheets/d/YOURSPREADSHEETID/edit?usp=sharing.

SheetName

Name of the specific Sheet you want to Get or Edit data in.

Range

The data range you wish to Get or Edit, such as A1:B10.

Dimension

The major dimension to Get or Edit data in.

For example, if you're using the Get Data command with the range A1:B10 and the dimension set to ROWS, it will return 10 arrays, each containing one row (e.g., A1-B1, A2-B2, etc.).

If the dimension is set to COLUMNS, it will return 2 arrays, each containing 1 column (e.g., A1-A10, B1-B10).

Values

(Applies to Append Data, Update Data, and Batch Update commands)
You can send values to the extension in two ways:

1. A string of values separated by |, representing individual cells. For example, if you want to insert values in the A1:A7 range (with the dimension set to COLUMNS), you can format them like this: Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday.
2. A stringified array, where values will be inserted into the selected range starting from the first position.
   1. Create an array with some entries.
   2. Use the Stringify Array command.
   3. Insert the stringified array variable in the Values field.

Buttons

Append by Value/Array

Appends values to a spreadsheet using either a single variable or an array. The values must be arranged in either a single column or a single row.

Update Data

Functions similarly to the Append Data command, but instead of adding new data, it updates (overwrites) data in the specified range.

Batch Update

Use the Batch Update command to perform multiple update data requests in one batch. Instead of sending each request separately, Batch Update groups them together to reduce API calls.

Remember, the write request quota is shared with other users of the extension, so it's best to group as many Update Data requests as possible.

1. Use the 'Google Sheets Batch Update' command and specify the number of requests.
2. Send your Update Data requests as usual.
3. The extension will queue all the requests and send them together once the last request is received. If you don't send all requests within 10 seconds, the queue is cleared and reset. Simply put, either all requests are sent at once, or none are sent.

Get Data

Retrieves data based on the specified range and dimension, and stores the results in an object.

Find Data

Searches for a string/value in a single column or row and returns its coordinates along with related values. The search must be an exact match and only finds the first occurrence.

Create Spreadsheet

Creates a new spreadsheet. The extension can only modify spreadsheets created specifically with this command.

Each created spreadsheet will have a hidden sheet added, which serves as a lookup for the Find Data command. Do not delete or rename this sheet.

Duplicate Sheet

Duplicates an existing sheet within the spreadsheet.

Add Sheet

Adds a new sheet to the selected spreadsheet.

Copy Paste

Copies data from a source range and pastes it into a specified target range.

Auto Fill

Automatically fills in additional data based on the existing data in the specified range.

Find Replace

Finds and replaces data within the specified range.

Split Text

Splits the text in a single column into multiple columns based on a specified delimiter.

Sort Range

Sorts rows of data based on the sort order for a specific column.

Randomize

Randomizes the order of the rows in the selected range.

Merge Cells

Merges a range of cells either by row or column.

Unmerge Cells

Unmerges cells that were previously merged.

Clear Data

Clears all data in the selected range.

Insert Range

Inserts new cells into a specified range, shifting existing cells either down or to the right.

the dimension to shift. ROWS shifts cells down, and COLUMNS shifts cells to the right. |

Delete Range

Deletes a range of cells.

Delete Duplicates

Removes rows in the specified range that contain duplicate values in the specified columns. This includes duplicates with variations in letter case, formatting, or formulas.

Privacy

This extension uses a custom AWS secure API to retrieve your refresh token, as this is only possible server-side and cannot be done from your Bridge. It never stores or shares any of your credentials and can only ever access public spreadsheets created with it.