1

I'm trying to write a piece of code that syncronize two sheets (the motivation behind it is here). I got this code from a SOF previous answer:

var sourceSpreadsheetID = SPREADSHEET.getId();
var sourceWorksheetName = "myData";
var destinationSpreadsheetID = "0AmEr9uNtZwnNdDFKMTVlbmZYZ3ZyOWM0aXpZS2twM1Z";
var destinationWorksheetName = "myData2";

function importData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(),thisData.getNumColumns());
  toRange.setValues(thisData.getValues());
}

function onEdit(){
  importData();
}

But when I open the sheet and edit it, nothing happens. Why?

PS - As I pointed before, I've already ask a similar question. But I think this is another question, because I'm not looking for the same answer from the previous question (How to make 2 sheets sync?) but a more specific question: what is preventing this code -- this solution -- of work?

Community
  • 1
  • 1
craftApprentice
  • 2,697
  • 17
  • 58
  • 86
  • Where's the global `SPREADSHEET` coming from? – Bryan P Dec 09 '13 at 01:35
  • You've got a function named `onChange()`, but you don't mention whether or not you've installed it as a Spreadsheet Change Trigger. You need to do that, or it won't run. – Mogsdad Dec 09 '13 at 02:23
  • Hi, @BryanP, the global `SPREADSHEET` is defined in my code. Yes, onChange(), is suppose to be a spreadsheet trigger. I also tried the onEdit() trigger and it didn't work. – craftApprentice Dec 09 '13 at 12:38
  • 1
    I was curious how it is defined b/c I'm seeing experiencing [this issue](https://code.google.com/p/google-apps-script-issues/issues/detail?id=3464) which I thought may be related. – Bryan P Dec 09 '13 at 16:27

3 Answers3

3

As a complement to the other answers, here are a few explanations :

In the documentation about spreadsheet triggers you will find all the details about what simple triggers can do (and can not do) and how to setup the so called "installable triggers".

The simple triggers run automatically without asking anything to the user modifying the spreadsheet and run as this "active user". For this reason, they can't do anything that requires authorization.

The installable triggers execute under the authority of the author of the script, in other words the person who created the trigger, and they need to be explicitly authorized by the script author. This also means that these function will access the script author data and not the user data.

A simple example : if a function triggered by an installable trigger sends an email, this email will be sent by the script author, not by the spreadsheet user... that's quite a difference.

What might have bring some confusion to you in the example you are showing is that the onEdit function does nothing else than calling another function... but if you look at it more thoroughly, the importData function is calling spreadsheet function and thus requires authorizations for spreadsheet access.

That's definitely why your onEdit does nothing.(please note that it is failing silently... another confusing detail :-)

Hoping this explanation will make it clearer.

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • As always, the most clear answer, thanks. Yes, my doubt was this: "What might have bring some confusion to you in the example you are showing is that the onEdit function does nothing else than calling another function... but if you look at it more thoroughly, the importData function is calling spreadsheet function and thus requires authorizations for spreadsheet access." Google should show a error msg when this happens. – craftApprentice Dec 12 '13 at 00:05
  • I followed the instructions to installed triggers and now it works like magic. – craftApprentice Dec 12 '13 at 00:29
0

Look at the help for onEdit. In there it says it cant do many things. Instead install your own trigger like onMyEdit

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36
0

Your function name onChange() suggests you may be using the wrong trigger type.

onEdit() and onChange() are triggered by different circumstances and the situation you describe means that you want onEdit()

Also as the commenter above states you will need to change the first line to the following to avoid returning 'undefined':

var sourceSpreadsheetID = SpreadsheetApp.getActiveSpreadsheet().getId();

Sam Scholefield
  • 772
  • 2
  • 6
  • 20