0

I have a script that reads through google drive folders and gives me a summery in google sheets. The write part of it uses getActive sheet. It takes a couple of minutes to excecute depending on how many files there are. I want to run the script every hour, so the the sheet will alway up to date with file deliveries. it works fine during the day where the sheet is active. But i got an error message list form google with failed atempts during the night where the sheet was not active, and i could therfore not use the active sheets command. Question is hiw to write to a sheet that is not active, if it is possible?

Cheers Mads

Ok figured it out: First i need to open the SpreadSheet (on the serner), thin get the sheet and then write to it: Final function is somthing like this:

function writeToClosedSheet(){ var ss = SpreadsheetApp.openByUrl("url_to_Sheet") var sheet = ss.getSheetByName('NameOfSheet'); sheet.appendRow( ["Mads found a solution"]); }

This works :)

Mads Tuxen
  • 46
  • 9
  • You don't need `openByUrl`. Just `getActive()` would work on the ``spreadsheet``. Only the `sheet` would have problems if spreadsheet is closed. – TheMaster Jan 29 '22 at 15:23

1 Answers1

0

replace .getActiveSheet() by .getSheetByName('mySheetName')

if the script is not bound to the spreadsheet, you have to call the spreadsheet by openbyid like const sheet = SpreadsheetApp.openById('SPREADSHEET_ID').getSheetByName('SHEET_NAME')

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Thank you Mike will that still require the spreadsheet (even if not the sheet) to be active? So maybe my question should be: how to write to a spreadSheet that is not open? – Mads Tuxen Jan 29 '22 at 13:11
  • OK thank you. I figured it out. Since the spreadsheet is not open i first need to open it. Then i can find the sheet, and finally do what needs to be done. So final function is somthing like: function writeToClosedSheet(){ var ss = SpreadsheetApp.openByUrl("url_to_Sheet") var sheet = ss.getSheetByName('NameOfSheet'); sheet.appendRow( ["Mads"]); } – Mads Tuxen Jan 29 '22 at 13:30
  • If the script is bound to the sheet, you can use getSheetByName, if the script is 'alone', you have to call the spreadsheet by openbyid like `const sheet = SpreadsheetApp.openById('SPREADSHEET_ID').getSheetByName('SHEET_NAME')` – Mike Steelson Jan 29 '22 at 13:32
  • I had some random errors with my scripts when running them automatically, and most of the disapeared when i went from using active sheet to using an absolute path. SpreadsheetApp.openByUrl(........) rather than getActvive.... – Mads Tuxen May 06 '22 at 08:47
  • or with `.openById('SPREADSHEET_ID')`as I mentionned. What is now the situation? Do you still encountered some issues? – Mike Steelson May 06 '22 at 09:29
  • No after changing from "active" the errors stopped :). I followed some tutorials where active vas used a lot but I think its better used in special cases. A newbie problem I suppose but hopefully others can read and get wiser. Cheers Mads – Mads Tuxen May 07 '22 at 10:52
  • So sorry i wasnt aware of this feature. Its totally accepted. Thank you. – Mads Tuxen May 09 '22 at 10:42