Extensions

I have created a walkthrough with examples showing how to use the extension.

Please follow buttons number 1-15 in the specified order while looking at your spreadsheet and commands inside the button to see what they’re doing to better understand how to use them.

You can watch the video guide (no talking + the video is missing the step where you need to make the new spreadsheet public) or simply read all the steps below. Note: This walkthrough is intended for English version of sheets. You might need to modify some commands (such as your sheet name or button 5. where it adds months in English) for it to work for other languages.

1. Create Spreadsheet
   We are starting with this button, because our extension can only modify spreadsheets we previously created with it.
   Go ahead and create Testing Spreadsheet. A new URL should open in your browser with your newly created spreadsheet (or you can go to https://docs.google.com/spreadsheets). You need to make the spreadsheet public , this extension can only work with public spreadsheets.
   We will work with this spreadsheet from now on, so keep it open to see what is happening when you press the next buttons.
2. Append by Value
   This command will append a few names to our sheet by using a single variable containing all the values. | separator marks one cell.
3. Append by Stack
   You can also append values by stack. In our example we append some points to our previously created user names.
4. Update by Value
   This is the same as Append by Value command, except it will override cells if they already contain values.
   In our example we update a cell with a formula that calculates the total amount of points of all the users and immediately returns the calculated value in a stack called ‘updated’. This is to show that you can write simple values into your sheets, but also complex formulas!
5. Update by Stack
   This is the same as Append by Stack command, except it will override cells if they already contain values. In our example we add some months to our list of users.
6. Get Data
   This command simply retrieves data by range.
   In our example we retrieve all users, their points and months by columns, which will be saved in 3 separate stacks: getData1 (containing names), getData2 (containing points) and getData3 (containing months).
7. Find Data
   To look up an individual user, we can use find data command.
   In this case we’re trying to look for Melonax within our usernames and we want to return the whole row. We want to know what her points and months are. All should be saved in foundData stack now.
8. Sort Range
   We can sort the users by their points, starting from the one with the most amount of points.
9. Auto Fill
   In our example we auto fill more months.
10. Find and Replace
    We search for ‘ju’ (in June and July) and replace it with ‘SUMMER:’. Because why not.
11. Split Text
    Now that we have a called SUMMER:ne and SUMMER:ly from our previous command, we can split them into 4 spells, using : as delimiter.
12. Batch Update
    Extremely useful command to update multiple ranges at once.
    In our example we add more users and their points to the list.
13. Randomize
    We shuffle the user points around and randomly assign them to our users. They will be surely happy about that…
14. Clear Data
    Now we will just clear all the points. Our users can start all over!
15. Delete Duplicates
    As you might have noticed, we’ve got two Melonax entries in our user list. With this command we can easily find the duplicate one and delete it.

A lot of buttons share the same parameters, which are going to be skipped in the Buttons section below.

SpreadsheetID
You can either fill it out manually for each button or set up spreadsheetID variable in your INIT button if you intend to use a single Spreadsheet.

Spreadsheet ID can be retrieved 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 particular Sheet you want to Get or Edit data in.

Range
Range you wish to Get or Edit data in, such as A1:B10.

Dimension
Major dimension to Get or Edit data in.

For example, if you use the Get Data command and set the range to A1:B10 and your dimension to ROWS, it will return 10 stacks, each containing one row (first stack will be A1-B1 values, second one A2-B2 values, etc.).

If you change your dimension to COLUMNS, it will return 2 stacks, each containing 1 column (first stack will be A1-A10 values and second stack will have B1-B10 values).

Values (applies to Append Data, Update Data and Batch Update commands)

You can send values to the extension in two different ways:

1. A string of values separated by | marking individual cells. For example, if you want to insert values in A1:A7 range (make sure dimension is COLUMNS), you can enter them like this: Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday.

   

   
2. A stringified stack. All values from the stack will be inserted into the selected rfalse, starting from position 0 and ending with the last position.
   1. Create a stack with some entries.
   2. Use Stack: Stringify command.
   3. Use String: Replace command on the newly stringified stack. Replace “ with ‘
   4. Insert the stringified stack variable in the Values field.

      

      

Append by value/stack
Appends values to a spreadsheet, either by a single variable or a stack. Must be either a single column or single row.

Update Data
Same as Append Data command, except it will update (=overwrite) data in the selected rfalse instead of appending them.

Batch Update
This is a very important button to understand if you want to perform update data batch requests. Instead of sending multiple single requests, use Batch Update button instead.

Please remember that you share write request quota with all other users using the extension, so try your best to group up your Update Data requests.

1. Send ‘Google Sheets Batch Update’ command and specify the amount of requests.
2. Send all your following Update Data requests as you normally would.
3. The extension will queue up the requests and wait for the last request to send them all at once. If you fail to supply all requests within the next 10 seconds, the queue will be deleted and resetted. Simply said, either all requests are sent or none.

Get Data
Retrieves data based on the range and dimension, and saves them into a stack.

Find Data
Matches a string/value to a cell in a single column or row and returns its coordinates + related values. Must be an exact match. Finds the first occurence only.

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

There will be a hidden sheet added to every spreadsheet you create. This sheet serves as sort of a look up sheet for Find Data command. Please don’t delete or rename it.

Duplicate Sheet
Duplicates an existing sheet.

Add Sheet
Adds a new sheet to the selected SfalsedSheet

Copy Paste Copies data from the source to the destination

Auto Fill
Fills in more data based on existing data.

Find Replace
Finds and replaces data in cells over a selected rfalse.

Split Text
Splits a column of text into multiple columns, based on a delimiter in each cell.

Sort Range
Sorts data in rows based on a sort order per column.

Randomize
Randomizes the order of the rows in a selected rfalse.

Merge Cells Merges cells for row or column.

Unmerge Cells Unmerges previously merged cells.

Clear Data Clears all data in the selected rfalse.

Insert Range
Inserts cells into a range, shifting the existing cells over or down.

Delete Range
Deletes a range.

Delete Duplicates
Removes rows within this range that contain values in the specified columns that are duplicates of values in any previous row. Removes duplicates with different letter cases, formatting, or formulas.