Modifying a Spreadsheet in Another User's Google Drive

Other Questions Google drive
1

Hey everyone! I’m trying to update a spreadsheet that belongs to another user in their Google Drive. I’ve managed to get the file, but I’m stuck on how to access the specific sheet I need to work with. Here’s what I’ve got so far:

let currentSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let sourceSheet = currentSpreadsheet.getSheetByName('BookList');
let dataRange = sourceSheet.getRange('A2:M');
let contentToMove = dataRange.getValues();

// Trying to access the target spreadsheet
let targetFile = DriveApp.getFileById('abc123xyz789');
let targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let targetSheet = targetSpreadsheet.getSheetByName('AllBooks');

// This is where I'm unsure what to do next

Can anyone help me figure out how to use the target file to get the right sheet and update it with my data? Thanks in advance!

2

I’ve dealt with this exact scenario in my work.

A crucial tip is to ensure you have edit access to the target spreadsheet before performing any modifications. Even with the correct code, lacking the proper permissions will halt your progress.

To elaborate, after retrieving the file with DriveApp.getFileById(), open the target spreadsheet using SpreadsheetApp.open(targetFile) instead of getActiveSpreadsheet(). Once the correct spreadsheet is open, get the desired sheet, update it with your data, and then use SpreadsheetApp.flush() to ensure the changes take effect.

Additionally, consider implementing try-catch blocks to handle potential errors gracefully when dealing with external resources.

3

I’ve encountered similar issues when working with external spreadsheets. The key is to open the target spreadsheet correctly. Instead of using SpreadsheetApp.getActiveSpreadsheet(), try SpreadsheetApp.open(targetFile). This should give you access to the spreadsheet in the other user’s Drive.

Here’s how you might modify your code:

let targetFile = DriveApp.getFileById('abc123xyz789');
let targetSpreadsheet = SpreadsheetApp.open(targetFile);
let targetSheet = targetSpreadsheet.getSheetByName('AllBooks');

After this, you should be able to work with targetSheet as you normally would. Remember to ensure you have the necessary permissions to edit the file. If you’re still having trouble, double-check the file ID and sheet name for any typos.

4

hey tom, i had this problem b4. u need to use SpreadsheetApp.open(targetFile) instead of getActiveSpreadsheet(). that’ll let u access the other person’s sheet. make sure u got permission tho or it won’t work. good luck!