0

I'm new to coding and have what for many is a simple problem. I want to transfer data from 2 cells in form spreadsheet to last row in different spreadsheet. I can get this to work if transferring to different tab within sheet but not to different sheet. What I have is below and is mostly editing of stuff I have found here:

function onEdit(e) {
if (e.range.getA1Notation() === 'C27') {

  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Sheet1"); //Sheet1
  var target = SpreadsheetApp.openById("https://docs.google.com/spreadsheets/d/1WIp-tBahgNJ_BTa0up89J5KMGoJOJyCfX6K6_leKFPM/edit#gid=1733591209");

  var target_sheet = target.getSheetByName("Archive");


  var values = [[formSS.getRange("C6").getValue(),
                 formSS.getRange("C23").getValue()]];


  target_sheet.getRange(last_row + 1, 1, values.length, values[0].length).setValues(values);
  }
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Conor Sheehy
  • 1
  • I've also used for last linebut haven't been successful with this either: target_sheet.getRange(target_sheet.getLastRow()+1, 1, 1, 2).setValues(values); – Conor Sheehy Aug 31 '20 at 13:35

1 Answers1

1

The problem is that you are using a simple trigger: onEdit(e).

From the documentation about simple triggers:

They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.

You need user permission to open another spreadsheet using a URL. That's why it won't work.

To get it done, you need to use an installed trigger instead. Rename your function and click here for step-by-step instructions on how to set it up.

Maciek
  • 131
  • 7