Blog

How-to Guide: Transfer Unique Notices Between Sheets in Google Sheets

Oct 3, 2024

Vincent Cumayas

Data Scientist

Alonzo Luis

Technical Writer

This how-to guide walks you through the process of setting up and using the transferUniqueNoticesDynamic() function to compare notices across sheets and transfer unique entries to a “Contracts” sheet.

---

Prerequisites

• You must have a Google Spreadsheet with at least two sheets starting with “notices” (e.g., notices (1), notices (2)).
• The notice sheets must contain a column labeled Notice Identifier to serve as the unique key for comparison.
• Basic familiarity with Google Apps Script and Google Sheets.

---

Steps to Set Up and Use the Function

1. Access the Script Editor

1. Open your Google Spreadsheet.
2. Click on Extensions → Apps Script.

2. Create the Function

1. In the Apps Script editor, delete any existing code.
2. Copy and paste the following function into the editor:

function transferUniqueNoticesDynamic() {
    const baseSheetName = 'notices';
    const contractSheetName = 'Contracts';
    const noticeIdentifierCol = 'Notice Identifier';

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheetNames = ss.getSheets().map(sheet => sheet.getName()).filter(name => name.startsWith(baseSheetName));
    sheetNames.sort((a, b) => {
        const numA = parseInt(a.match(/\d+/g)) || 0;
        const numB = parseInt(b.match(/\d+/g)) || 0;
        return numB - numA;
    });

    if (sheetNames.length < 2) {
        Logger.log('Not enough sheets to compare.');
        return;
    }

    const latestSheetName = sheetNames[0];
    const prevSheetName = sheetNames[1];
    const latestSheet = ss.getSheetByName(latestSheetName);
    const prevSheet = ss.getSheetByName(prevSheetName);

    let contractSheet = ss.getSheetByName(contractSheetName);
    if (!contractSheet) {
        contractSheet = ss.insertSheet(contractSheetName);
    } else {
        contractSheet.clear();
    }

    const latestData = latestSheet.getDataRange().getValues();
    const prevData = prevSheet.getDataRange().getValues();
    const latestNoticeIndex = latestData[0].indexOf(noticeIdentifierCol);
    const prevNoticeIndex = prevData[0].indexOf(noticeIdentifierCol);

    if (latestNoticeIndex === -1 || prevNoticeIndex === -1) {
        Logger.log('Notice Identifier column not found.');
        return;
    }

    const prevNoticesSet = new Set(prevData.slice(1).map(row => row[prevNoticeIndex]));
    const uniqueNotices = [];

    for (let i = 1; i < latestData.length; i++) {
        const noticeIdentifier = latestData[i][latestNoticeIndex];
        if (!prevNoticesSet.has(noticeIdentifier)) {
            uniqueNotices.push(latestData[i]);
        }
    }

    if (uniqueNotices.length === 0) {
        contractSheet.appendRow(['There is no new contract yet.']);
        Logger.log('There is no new contract yet.');
        return;
    }

    contractSheet.appendRow(latestData[0]);
    uniqueNotices.forEach(row => {
        contractSheet.appendRow(row);
    });

    Logger.log(`Unique notices from ${latestSheetName} not found in ${prevSheetName} have been transferred to the Contracts sheet.`);
}

3. Run the Script

1. In the Apps Script editor, click on the dropdown next to the run button.
2. Select transferUniqueNoticesDynamic.
3. Click on the Run button (▶️).
4. If prompted, authorize the script to access your spreadsheet.

4. Review the Result

• After running the script, a new sheet titled “Contracts” will be created, containing only the unique notices found in the latest sheet.
• If no unique notices are found, the message "There is no new contract yet." will be displayed in the “Contracts” sheet.

---

Reference: transferUniqueNoticesDynamic() Function

The transferUniqueNoticesDynamic() function is used to compare notices between the latest two sheets in a Google Spreadsheet and transfer unique rows to a “Contracts” sheet.

---

Parameters

• baseSheetName (string): Prefix used to identify sheets that contain notice data. This is set to 'notices' by default.

• contractSheetName (string): The name of the sheet where unique notices will be transferred. The default is 'Contracts'.

• noticeIdentifierCol (string): The column that holds the unique notice identifier. The default is 'Notice Identifier'.

---

Behavior

1. Sheet Identification:

   • It retrieves all sheets in the Google Spreadsheet whose names begin with 'notices' and sorts them in reverse order to identify the two most recent sheets.

2. Validation:

   • The function verifies that at least two 'notices' sheets exist and that both sheets contain a 'Notice Identifier' column.

3. Comparison:

   • It extracts all values from the notice sheets and compares the notice identifiers between the latest and previous sheets. Any notices in the latest sheet that are not present in the previous sheet are considered unique.

4. Data Transfer:

   • If unique notices are found, they are appended to a “Contracts” sheet, along with the header row. If no unique notices are found, the function adds a message stating "There is no new contract yet."

---

Edge Cases

• Less than two sheets: If there are fewer than two 'notices' sheets, the function logs: Not enough sheets to compare.
• Missing Notice Identifier: If the 'Notice Identifier' column is not found in either sheet, the function logs: Notice Identifier column not found.
• No Unique Notices: If no unique notices are found, the function logs: There is no new contract yet. and appends a row with the same message to the “Contracts” sheet.

---

Example Log Outputs

• Unique notices from notices (2) not found in notices (1) have been transferred to the Contracts sheet.
• There is no new contract yet.

---

With this you’ll be able to efficiently compare sheets in Google Sheets and automatically transfer all unique data from an updated notices sheet.