Google sheet created by other user can't be found by apps script

43 Views Asked by At

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.

0

There are 0 best solutions below