2

I'm new to Google Sheets and apps scripts. This is my first ever question. I apologize in advance if I don't get it quite right.

  • Google Sheets file Staff Roster sheet Roster contains names, contact info, and some private data for staff members. It is shared with only executives.
  • Google Sheets file Staff Contacts sheet Contacts is a contact list formatted to work nicely on phones. It contains only name, phone, and email and is shared with all staff members. They have view-only access.
  • Contacts uses importrange() to import the data it needs from Roster.
  • When an email address is changed in Roster, that change may necessitate widening or narrowing the email address column in Contacts.
  • I have created an onOpen() function in Contacts that resizes columns whenever Contacts is opened for editing:
function onOpen(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("Contacts");
    sh.autoResizeColumns(2,4);
}

This works fine, but requires me to open Contacts whenever a change is made to Roster in order to make sure columns are widened or narrowed if needed.

Question: Is there a way to trigger a script in Contacts when a change is made in Roster? There are other columns with the same issue; I have simplified by mentioning only email.

Research: I have read about a dozen posts that seem related, but I haven't found one that answers this particular question. This has to be a common question. I'm probably just not using the correct search terms to get the answer because I'm so new to apps scripts.

1
  • I'd like to welcome you to Web Apps and thank you for the effort you invested in your question. It was very clear and detailed. A- (half mark deductions for unnecessary meta-commentary at the start and end😉) What a great first post!
    – Blindspots
    Nov 30, 2023 at 17:14

2 Answers 2

1

Use Installable Trigger

Instead of using the simple trigger onOpen(e) bound to Staff Contacts, use an installable trigger in Staff Roster to make the change in Staff Contacts whenever Staff Roster is edited.

SpreadsheetApp.openById(id).getSheetByName("Contacts") where id is the spreadsheet ID of Staff Contacts will allow you to access the second spreadsheet from the script.

This approach requires an installed trigger (as opposed to simple) which you can install manually, or install using a function (the approach taken below).

Installed triggers require approving additional authorizations when the trigger is first installed.

Sample Script

  1. The sample script below resizeContacts(e) is bound to Staff Roster and the trigger is installed by manually running createEditTrigger().
  2. Every time the spreadsheet Staff Roster is edited, the active sheet is checked to see if it is named Roster as well as if any of the edited columns are 2-5.
  3. If both conditions are true, columns 2-5 (4 columns, starting with column 2), of sheet Contacts in the spreadsheet Staff Contacts, are resized.

Presumably, you could also manage the data in Contacts based on this same trigger rather than using the IMPORTRANGE function as you are currently, thus making the calls for data only when actually necessary (data has changed).

  • remember to replace ssId with the ID for your Staff Contacts spreadsheet.
// run this once to install trigger
function createEditTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('resizeContacts') 
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

// this is the main function
function resizeContacts(e) {

  const range = e.range;
  const sheetA = range.getSheet().getSheetName();
  const start = range.getColumn();      // first column
  const count = range.getNumColumns();  // column count

  // Staff Roster info
  const nameA = "Roster";        // sheet name to match 
  const match = [ 2, 3, 4, 5 ];  // any one column to match
    
  // Staff Contacts info
  const ssId = "1Veb5WQf0erfghCV4YkmPcjtd5AswVr0kHIZt-Lg9AI";
  const size = { c: 2, n: 4 };  // resize start 'c'olumn and 'n'umber
  const nameB = "Contacts";

  if (sheetA == nameA) {

    // all edited column numbers 
    const cols = Array(count).fill().map((e, i) => i + start);
      
    // check if any of match in cols
    if (match.some(c=> cols.indexOf(c) >= 0)) {  

      // open 'Contacts' for resizing       
      const sheetB = SpreadsheetApp.openById(ssId).getSheetByName(nameB);
      sheetB.autoResizeColumns(size.c, size.n);

      }

    }

}
3
  • 1
    Thanks @Blindspots for this help, especially for going to the trouble to write a script. I'll give this a try sometime this week and report back. Nov 30, 2023 at 4:48
  • Again, thank you @Blindspot. Your solution worked perfectly. Being new to Google Sheets, I'm pleasantly surprised that such a thing can be done. I haven't done the importrange() fix yet because there's more complexity than I showed in my question (wanted to keep it simple). Because these sheets will be installed as a package by multiple groups, I wanted to save them from running the script to install the trigger, so I put it within an onOpen function. Would it be appropriate for me to use "Answer Your Question" to show this refinement? I don't want to take away from the excellence of yours. Nov 30, 2023 at 19:51
  • Of course! I'm interested to see the changes.
    – Blindspots
    Nov 30, 2023 at 20:21
0

The answer given by @Blindspots is the one to look at. This is just an additional tidbit to share. The spreadsheet in question will be distributed to various recipients on an ongoing basis and I didn't want each recipient to have to understand about installing the trigger, so I added this code (I subscribe to the else-less, early-return coding style):

function onOpen(e)
{
  const trigger = "rosterChange";
  const triggers = ScriptApp.getProjectTriggers();

  for (let i=0; i<triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == trigger) {
      return;
    }
  }

  // Create the trigger.

  const ss = SpreadsheetApp.getActive();

  ScriptApp.newTrigger(trigger) 
      .forSpreadsheet(ss)
      .onChange()
      .create();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.