Instantly share code, notes, and snippets.

Embed
What would you like to do?
Copy notes between Google Sheets
// If you make a mistake, this will clear all notes from the child sheets
function clearChildNotes() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
var buildings = sheet.getRange(1, 1, sheet.getLastRow(), 3).getValues();
for(var i=0; i<buildings.length; i++) {
SpreadsheetApp.openByUrl(buildings[i][1]).getSheetByName('Sheet1').clearNotes();
}
}
function syncNotes() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var data = sheet.getRange(2,1,sheet.getLastRow(), sheet.getLastColumn()).getValues();
for (var i=0; i<data.length; i++) {
if(data[i][0]) {
var url = getSheetUrl(data[i][0])
if(url) {
// This returns an array of notes as an object so you can write a row at a time rather than a cell at a time.
var notes = sheet.getRange(i+2, 1, 1, sheet.getLastColumn()).getNotes();
// Set the value on the child sheet
var child = SpreadsheetApp.openByUrl(url[0]);
child.getSheetByName('Sheet1').getRange((i+2 - url[1]), 1, 1, notes[0].length).setNotes(notes);
}
}
}
}
// Helper function to get the sheet URL for the child sheet
function getSheetUrl(building) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
var buildings = sheet.getRange(1, 1, sheet.getLastRow(), 3).getValues();
var url;
buildings.forEach(function(el) {
if(el[0].toString() == building) {
url = [el[1], el[2]]
}
})
return url;
}
function updateOffset() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get the offset helper and data as a 2D array
var offsetsSheet = ss.getSheetByName('Sheet2');
var offsets = offsetsSheet.getDataRange().getValues();
// Get the staff list as a 2D array
var torSheet = ss.getSheetByName('Sheet1');
var torList = torSheet.getDataRange().getValues();
// Loop each building
for(var i=0; i<offsets.length; i++) {
for(var j=0; j<torList.length; j++) {
if(offsets[i][0] == torList[j][0]) {
offsetsSheet.getRange(i+1, 3).setValue((j-1));
// Stop this loop and index i+1
break;
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment