SheetMerge allows you to program multiple Google Sheets at once minus the hassle of defining hundreds of things.
Here is a brief tutorial on how to install. If you are interested in how to use, see here
-
Go to Google Sheets
-
Open new Google Script through Extensions
-
Once in the script, click the plus button above "Libraries"
-
Paste the following ID and press look up:
1MYnIAX-fSzeMQXHUGTrphcyFiwcSCC-fybI4CjqWj7Yv_E_5EymHxiAa
- Select a version and apply
SheetMerge provides custom commands that let you define what you want to do to another Google Sheet. In other words, you can edit two at once.
First, you will need a link. In my case, I will use %link% as a replacement for an actual one.
Here is an example of how to pair a new sheet:
var link = "%link%";
function yourFunction() {
var active = SheetMerge.getActiveSheet(link);
Logger.log(active);
}If the logger logs the active sheet used in the link, than the program is fully installed.
There are many more things you can do besides this, and here is a full list of all of them
Here is a script on how to get the properties of a cell:
var link = "%link%";
function getValues() {
let text = SheetMerge.getValue("A1", link),
font = SheetMerge.getFontFamily("A1", link),
size = SheetMerge.getFontSize("A1", link);
Logger.log(text + ", " + font + ", " + size);
}If you do not already have a sheet to edit, you can easily make one like this:
function createNew() {
var sheet = SheetMerge.createNew('name'); //upon running, this returns the url of the new sheet for you to edit.
}You can even use the SheetMerge library to edit the sheet remotely!
function changeText() {
SheetMerge.setCell("A1", "Test text", link);
Logger.log(SheetMerge.getValue("A1", link));
}Editing Sheets are easy when it comes to SheetMerge
var link = "%link%";
function editSheets() {
var name = SheetMerge.getSheetName(link);
var id = SheetMerge.getSheetId(link);
//Gets the name and id of the open sheet remotely
SheetMerge.renameActiveSheet("newName", link);
//Renames the sheet of your choice
SheetMerge.insertSheet("sheetName", link);
SheetMerge.copySheet("newName", link);
SheetMerge.duplicateActiveSheet(link);
//Insert and copy sheets
SheetMerge.deleteActiveSheet(link);
SheetMerge.deleteSheet("sheetName", link);
//Delete unnecessary ones
}Lots of font work is involved, so be warned!
var link = "%link%";
function getFonts() {
let color = SheetMerge.getFontColor("A1", link),
size = SheetMerge.getFontSize("A1", link),
style = SheetMerge.getFontStyle("A1", link),
weight = SheetMerge.getFontWeight("A1", link),
family = SheetMerge.getFontFamily("A1", link);
//Get font properties
SheetMerge.setFontColor("A1", "#fff", link);
SheetMerge.setCellFontSize("A1", "10", link);
SheetMerge.setFontStyle("A1", "italic", link);
SheetMerge.setFontWeight("A1", "bold", link);
SheetMerge.setFontFamily("A1", "times new roman", link);
//Change font properties
}| Property | Type | Description |
|---|---|---|
| getActiveSheet(link) | Sheets | Gets the current sheet of a Spreadsheet |
| getSpreadsheetName(link) | Sheets | Gets the name of a spreadsheet from a link |
| getSpreadsheetID(link) | Sheets | Gets the ID of a sheet from it's link |
| copySpreadsheet(newName) | Sheets | This function copies the active spreadsheet and allows users to provide a custom name for the copied spreadsheet. |
| copySpreadsheet() | Sheets | This function copies the active spreadsheet and automatically names the copy as "Copy of [original spreadsheet name]" |
| copySheet(sourceSheet, newSheetName) | Sheets | This function copies a specific sheet within the active spreadsheet and assigns a new name to the copied sheet. |
| getSheetUrl() | Sheets | This function retrieves the URL of the active spreadsheet |
| getSheetName(link) | Sheets | Gets the name of the active sheet |
| getURL(id) | Sheets | Gets the url of a Spreadsheet from the ID |
| copySheet(newname, link) | Sheets | Copies a Spreadsheet |
| deleteActiveSheet(link) | Sheets | Deletes the active sheet |
| deleteSheet(sheetName, link) | Sheets | Deletes a sheet by its name |
| duplicateActiveSheet(link) | Sheets | Duplicates the active sheet |
| renameActiveSheet(newName, link) | Sheets | Renames the active sheet |
| insertSheet(name, link) | Sheets | Inserts a new sheet |
| getviewers(link) | People | Gets a list of all the active viewers and their emails |
| getOwner(link) | People | Gets the owner of the document |
| getA1(range, link) | Cells | Gets the A1 notation of a range |
| getCellBorder(range, link) | Cells | Gets the border style of a range |
| getFontColor(range, link) | Cells | Gets the font color of a range |
| getFontSize(range, link) | Cells | Gets the font size of a range |
| getCellRow(range, link) | Cells | Gets the row position of a range |
| getCellColumn(range, link) | Cells | Gets the column position of a range |
| getValue(range, link) | Cells | Gets the text of a cell or range |
| getLastColumn(range, link) | Surroundings | Gets the last column used in the spreadsheet |
| getLastRow(range, link) | Surroundings | Gets the last row used in the spreadsheet |
| getFontStyle(range, link) | Fonts | Gets the font style of a range |
| getFontFamily(range, link) | Fonts | Gets the font of a range |
| getFontWeight(range, link) | Fonts | Gets the font weight of a range |
| setCell(cell, text, link) | Setting | Sets a cell's text |
| setCellBorder(range, top, left, bottom, right, link) | Setting | Sets the border weight of a range |
| setCellFontSize(cell, size, link) | Setting | Sets the font size of a cell/range |
| setFontColor(range, color, link) | Setting | Sets the font color of a cell/range |
| setFontStyle(range, style, link) | Setting | Sets the font style of a cell/range |
| setFontFamily(range, font, link) | Setting | Sets the font of a cell/range |
| setFontWeight(range, weight, link) | Setting | Sets the font weight of a cell/range |
| setRowHeight(row, height, link) | Setting | Sets the height of a specified row |
| setColumnWidth(column, width, link) | Setting | Sets the width of a specified column |
| setFormula(range, formula, link) | Formulas | Sets the fomula of a cell |
| getFormula(range, link) | Formulas | Retrieves the formula of a cell |
| clearRange(range, link) | Deletion | Clears the text of a range |
| deleteRow(rowNum, link) | Deletion | Deletes a row |
| deleteRows(row, howMany, link) | Deletion | Deletes multiple rows |
| deleteColumn(columnNum, link) | Deletion | Deletes a column |
| deleteColumns(column, howMany, link) | Deletion | Deletes multiple columns |
| setTabColor(color, link) | Misc | Honestly I don't even know what this one does either :'> |
Check out my profile or share it! (This means a lot to me) Also a huge thanks to my contributers!
https://github.com/WillDev12
https://github.com/cloud-dark
Footnotes
-
Copyright © WillDev12 2023 ↩