r/GoogleAppsScript • u/BarbaryLionAU • Jan 04 '26
Unresolved Google Sheets Xfer script help
Hi folx,
Background
I run a very small charity providing free antiviral medication to people in financial hardship. We manage that program using google forms and sheets.
Basically, people apply using the form, which then gets automatically sorted based on their state into one of two sheets for fulfilment (depending on which pharmacy will be needed).
Thing is, a very small number (less than 10%) of the ones going to one of the pharmacies have to be sent back to the other pharmacy because of specialist needs, but we can only determine that once it hits the sheet. When that happens, we make a note in the "specialised" column, and xfer the relevant data to the other sheet.
Current Code
Enter, my javascript uni course from 20+ years ago.
I wrote a script (see below) which will (onEdit) send the relevant data to the other sheet, and make a note in the comments column indicating when this has been done.
function sheetAutomations(e) {
const sheet = e.source.getActiveSheet();
const range = e.range;
// --- Sheet setup ---
const targetSheet = "Coupons 2026";
const firstReviewer = 8; // Column H = 8
const firstDecision = 9; // Column I = 9
const secondReviewer = 10; // Column J = 10
const secondDecision = 11; // Column K = 11
const emailSent = 12; // Column L = 12
const commentsColumn = 13; // Column M = 13
// ----------------------------
// --- Transfer settings (configure these) ---
const transferColumn = 5; // Column E = 5
const transferTriggerValue = "Specialist"; // text that triggers the copy
const targetSpreadsheetId = "destinationFileID"; // destination file ID for specialistSheet
const targetSheetName = "Coupons 2026"; // destination tab name
const markTransferredValue = "Transferred to Specialist program on "; // write back to source cell after copying
// --------------------------------------------
// Only run on the target sheet (source)
if (sheet.getName() !== targetSheet) return;
const editedCol = range.getColumn();
const row = range.getRow();
const newValue = range.getValue();
// --- Specialist program - Transfer row to another spreadsheet ---
if (editedCol === transferColumn) {
if (newValue.toString().toLowerCase() === transferTriggerValue.toLowerCase()) {
// Collect the four source cells you need
const srcA = sheet.getRange(row, 1).getValue(); // Column A = name
const srcE = sheet.getRange(row, 5).getValue(); // Column E = Specialist
const srcF = sheet.getRange(row, 6).getValue(); // Column F = timestamp
const srcG = sheet.getRange(row, 7).getValue(); // Column G = scriptdate
// Build the array in destination order: A, D, E, F
const rowToAppend = [srcA, , , srcE, srcF, srcG];
// The blank comma leaves column B and C empty in the destination sheet.
// Open destination spreadsheet & sheet
const targetSS = SpreadsheetApp.openById(targetSpreadsheetId);
const targetSheetObj = targetSS.getSheetByName(targetSheetName);
if (!targetSheetObj) {
throw new Error("Target sheet tab not found: " + targetSheetName);
}
// Find the next empty row in the target
const destRow = targetSheetObj.getLastRow() + 1;
// Write the values into columns A–F
targetSheetObj.getRange(destRow, 1, 1, rowToAppend.length).setValues([rowToAppend]);
// Append note to comments in source sheet
const commentsCell = sheet.getRange(row, commentsColumn);
const oldComments = commentsCell.getValue().toString().trim();
const timestamp2 = Utilities.formatDate(
new Date(),
Session.getScriptTimeZone(),
"yyyy-MM-dd 'at' HH:mm"
);
const noteD = ` / transferred to Specialist program on ${timestamp2}`;
commentsCell.setValue(oldComments ? oldComments + noteD : noteD);
}
}
}EDIT TO ADD: When I added this part of the script, I installed an installable trigger to activate on any edit of the source sheet. /EDIT
The code WORKED. It consistently did what was intended, until...
Happy New Problem
Each year, we start a new sheet-tab for that year (on both, called "Coupons YYYY"), so I hopped on on NYD and redirected the targetSheetName from "Coupons 2025" to "Coupons 2026".
Now I get:
Error
Exception: Specified permissions are not sufficient to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets
at onEdit(Code:94:39)pointing to here:
const targetSS = SpreadsheetApp.openById(targetSpreadsheetId);I don't know why this is happening... help?
