How to reference an external spreadsheet with Google Apps Scripts

Question

Update: It worked for 5 minutes then stopped working again. Not exactly sure why it's only working part of the time?

This sounds like it's supposed to be simple but I'm only finding information on "getActive" and my function works if I'm using all internal sheets but I tried using openById and nothing happened and openByUrl returns an error. The following is a simplified example of what I'm trying to do:

  var store_data_sheet = SpreadsheetApp.openByID('ID here..').getSheetByName('Data'); 
  store_data_sheet.getRange('A1').setValue('testing')
  store_data_sheet.insertRows(1);

I'm sure I'm just using the wrong keywords or maybe this isn't even allowed but multiple users will be using their own copy of a specific sheet that runs this function and I'd just like to take certain cell's data and copy it to one external sheet (which is why it also inserts a row so it can push down entries) Maybe I'm using the wrong "ID"? I assumed the number inside the URL was the ID. If it's something more specific, please let me know.

Like I said, this works flawlessly if I'm referencing an internal sheet. I'd like to be able to do the same exact thing with an external spreadsheet if possible. Thank you!

What do you mean by external spreadsheet? Is that a spreadsheet that is in another account? If so, has it been shared with you? — Cooper
It's just my account still. I updated the post to say this did work for a minute then it stopped again even though the code is the same. I'm a little confused. — Ali
Now it's working again. I guess this isn't a scripting issue. — Ali
I found the issue. This function also renames the sheet file so if there is anything in the cell that is used to rename the file, it won't run the storing functions. I either need to run these before the renaming or take out the renaming aspect. ---moving these before the renaming part fixed the problem. Oops, sorry. haha — Ali
Can you provide an answer and mark it as accepted so many people can benefit from it? — Kessy

Ali Ali · Accepted Answer · 2020-05-19T17:03:03

I found the issue. This function also renames the sheet file so if there is anything in the cell that is used to rename the file, it won't run the storing functions. I either need to run these before the renaming or take out the renaming aspect. ---moving these before the renaming part fixed the problem.

How to reference an external spreadsheet with Google Apps Scripts

1 Answers