I've tried to write some scripts in google sheets. They work just fine when I exececute them. However when an different user runs the script (Script 1 as shown below) in which a google sheet is generated, the file can't be found by the subsequent script. When I run the script that generates the new google sheet, and different user runs the subsequent script (Script 2), is does work. By apps script an ASCII file is imported in google sheets, after that below script is executed, which creates the new google sheet.
SCRIPT 1
function Copy_Active_Sheet_To_New_Workbook() {
// Get active spreadsheet and sheet
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = activeSpreadsheet.getActiveSheet();
// Get the name from cell A1
var newWorkbookName = activeSheet.getRange("A1").getValue();
if (!newWorkbookName) {
Logger.log("Please provide a name in cell A1 to use for the new workbook.");
return;
}
// Specify the folder ID where you want to place the new workbook
var folderId = "1l6XADKw60CQJjKbHJ2I6BATyMfrHwY92"; // Replace with the actual folder ID
// Create a new spreadsheet with the specified name
var newSpreadsheet = SpreadsheetApp.create(newWorkbookName);
// Get the ID of the newly created spreadsheet
var newSpreadsheetId = newSpreadsheet.getId();
// Move the newly created spreadsheet to the specified folder
DriveApp.getFileById(newSpreadsheetId).moveTo(DriveApp.getFolderById(folderId));
// Get the new sheet name
var newSheetName = activeSheet.getName();
// Rename the copied sheet in the new spreadsheet to match the original sheet name
newSpreadsheet.getSheets()[0].setName(newSheetName);
// Get the data range in the active sheet
var dataRange = activeSheet.getDataRange();
// Get values from the active sheet
var values = dataRange.getValues();
// Get the new sheet in the new spreadsheet
var newSheet = newSpreadsheet.getSheets()[0];
// Set values and formulas in the new sheet
newSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
// Clear data in the original sheet
activeSheet.clear();
Logger.log('Sheet copied to new workbook: ' + newSpreadsheet.getUrl());
}
SCRIPT 2(only the part searching for the file created in script 1)
....
// Zoek naar bestanden in de specifieke Drive-map
var map = DriveApp.getFolderById('1l6XAD.......'); // Vervang 'driveMapId' door de ID van de map waarin je wilt zoeken
var bestanden = map.searchFiles('fullText contains "' + tekstOmTeZoeken + '"');
// Logging toevoegen om te controleren of de tekst om te zoeken correct is
Logger.log("Tekst om te zoeken: " + tekstOmTeZoeken);
....
Does anyone have any idea how to solve this?
I already tried to make adjustments in script 1 so that the time settings not user depended:
// Set the spreadsheet's locale to Netherlands - this one works
newSpreadsheet.setSpreadsheetLocale('nl_NL');
// Set the timezone for the new spreadsheet to "Europe/Paris" - this one doesn't work
newSpreadsheet.setSpreadsheetTimeZone('Europe/Paris');
I tried to change the user access settings form Editor to Owner, that did not solve the problem.