Re: [Apps-Script] Script runs manually but error when run onEdit

[George Ghanem]

The issue is not ownership but more a question of what trigger you are using.

The onEdit is a simple trigger and you can not call any Google Services or access anything outside of the spreadsheet itself where the code is present.

You can achieve what you require by installing a trigger manually and doing the required authorization for it to run. (Change the name of function from onEdit also).

On Mon, Nov 4, 2024, 6:09 a.m. Ian Dodd <i...@goodmanagement.co> wrote:

Hi all,

Full disclosure, I have not written code for almost 20 years and heavily relying on Chat GPT to help me (sorry in advance but needs must...)!

I have multiple workbooks which need the same data imported from a single worksheet called "Lookups". The data to be transferred is in a Worksheet called Admin in Columns A:AB.

The workbooks that need updating may change periodically or I might want to add more workbooks that require the information to be imported, so I have a worksheet (Data Imports) with the URL's for the files to be updated listed in column B and in column CV I have an x to indicate that this workbook requires the information to be imported. I need to set up multiple scripts which will act similarly, CV just happens to be the one I chose for testing).

When I run the script manualy, all works fine, data gets transferred and everything is happy.

When I try to get it to run when the Worksheet "Data Imports" is edited (e.g. adding an x to the CV column to indicate another workbook that needs the data), I get the error message "Error in Lookups function: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets".

All workbooks are in my account so permissions should not be an issue.

Here's the script I am currently using:

// Function that triggers when an edit is made to the "Admin" or "Data Imports" sheets

function onEdit(e) {

// Check if the event object is defined

if (!e) {

Logger.log('No event object. Exiting onEdit.');

return; // Exit if the event object is undefined

}

const editedSheetName = e.source.getActiveSheet().getName();

Logger.log(`Edited sheet: ${editedSheetName}`);

// Check if the edit is on the correct sheets

if (editedSheetName === 'Admin' || editedSheetName === 'Data Imports') {

Logger.log('Calling Lookups due to edit on Admin or Data Imports');

Lookups();

} else {

Logger.log('Edit was not on Admin or Data Imports; no action taken');

}

}

// Main function to perform the data transfer

function Lookups() {

try {

const sourceSpreadsheetId = '[URL for Lookups Worksheet - Removed for security]';

const sourceSheetName = 'Admin';

const sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);

const sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);

const sourceData = sourceSheet.getRange('A:AB').getValues();

const dataImportsSheet = sourceSpreadsheet.getSheetByName('Data Imports');

const dataImportsRange = dataImportsSheet.getDataRange().getValues();

for (let i = 1; i < dataImportsRange.length; i++) {

let destinationSpreadsheetUrl = dataImportsRange[i][1];

const includeInTransfer = dataImportsRange[i][99];

if (destinationSpreadsheetUrl && includeInTransfer.toLowerCase() === 'x') {

const match = destinationSpreadsheetUrl.match(/\/d\/([a-zA-Z0-9-_]+)/);

if (!match || !match[1]) {

Logger.log(`Invalid URL format for destination spreadsheet: ${destinationSpreadsheetUrl}`);

continue;

}

const destinationSpreadsheetId = match[1];

try {

const destinationSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetId);

const destinationSheet = destinationSpreadsheet.getSheetByName('Lookups');

destinationSheet.getRange('A:AB').clearContent();

destinationSheet.getRange(1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);

} catch (error) {

Logger.log(`Failed to transfer data to workbook with ID ${destinationSpreadsheetId}: ${error.message}`);

}

Utilities.sleep(500); // Pause for 500 milliseconds

}

}

} catch (error) {

Logger.log(`Error in Lookups function: ${error.message}`);

}

}

Any help or advice would be much appreciated.

Thanks,

Ian

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/4947b08d-96ce-45e0-a161-ae0bec11c3a9n%40googlegroups.com.