Save data from one Google spreadsheet to another to create a database

Question

I'm new to Google App Scripts and JavaScript as I work on the marketing side. I'm currently trying to create a database in Google Sheets to use in DataStudio as I want to blend data from various sources.

I have Google Sheets with a sheet to feed the data (through add-ons) and another sheet which I intend to use as the database. What I'm trying to do is to save data in the database, new data being saved at the end of the spreadsheet each time (I will set a time-based trigger when my script works). I've written a script using Google's documentation but it's only copying the first line of my Data sheet to the database. I would need it to copy all the content from the data sheet to the database sheet.

Here is the script I have for now :

// function to save data
function saveData() {
  // starts with active sheet for data entry
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Data");

  // collects values in data entry row
  var url = sheet.getRange("Data!A1:A").getValues();
  var follower_count = sheet.getRange("Data!B1:B").getValues();
  var date = sheet.getRange("Data!C1:C").getValues();  


  // identifies and adds data to Database
  var data1 = ss.getSheetByName("Database");
  var aVals = data1.getRange("A1:A").getValues();
  var aLastRow = aVals.filter(String).length;

  Logger.log(aLastRow);

  var newAttData = [[url,follower_count,date]];
  Logger.log(newAttData);

  data1.getRange(aLastRow +1,1,1,3).setValues(newAttData);

}

I've tried to change the range width and length in the last line but I always get an error:

Incorrect range height, was 1 but should be 3 (line 31, file "Code")

I've spent a lot of time on this but I can't get it to work. Any help would be great !

If you always get an error, then you should perhaps include the error in your question. — tehhowch
Thanks for the answers ! This is the error I get : Incorrect range height, was 1 but should be 3 (line 31, file "Code") I get this error when I change : data1.getRange(aLastRow +1,1,1,3).setValues(newAttData); to: data1.getRange(aLastRow +1,1,3,3).setValues(newAttData); — Thomas Aubry
Sounds like a problem of arrays and objects, when you getValues you might be getting it in arrays but while pasting it should be an object. Please verify. — ledzee
I'm not sure to understand, but when I define "var url = " for example isn't it an object ? Sorry I'm pretty to new JS. — Thomas Aubry

Cooper Cooper · Accepted Answer · 2018-05-11T00:44:39

As Described in the documentation:

setValues(values) Sets a rectangular grid of values (must match dimensions of this range).

var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0];

// The size of the two-dimensional array must match the size of the range. var values = [ [ "2.000", "1,000,000", "$2.99" ] ];

var range = sheet.getRange("B2:D2"); range.setValues(values);

Reference

Save data from one Google spreadsheet to another to create a database

1 Answers