Google-sheets – Move data script

google sheetsgoogle-apps-script

I am setting up a script to copy data from one location and paste values into another location (same tab). However it is only picking up the first line. How can i get it to pick up the whole range ?

function moveValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange("RUNSHEET!H4:AC20");
  var destSheet = ss.getSheetByName("RUNSHEET");
  destSheet.appendRow(source.getValues()[0]);
}

Best Answer

You have to remove

destSheet.appendRow(source.getValues()[0]);

by something else because

appendRow only adds one row of values

and

source.getValues()[0]

returns only the values of first row of source

That something else could be one of the methods getRange and then setValues(source.getValues()) or you could use copyTo

There are a lot of examples on this site.

Reference

https://developers.google.com/apps-script/guides/sheets

Related Solutions

Google Sheets – How to Import JSON Using Google Apps Script

As JPV pointed out, the line

var jsonData = ImportJSON(apiUrl);

is not valid in Apps Script (unless you have defined ImportJSON function somewhere). To fetch something from an external URL in Apps Script, one uses UrlFetchApp. So the line could be

var jsonData = JSON.parse(UrlFetchApp.fetch(apiUrl).getContentText());

After this, jsonData is a JavaScript object parsed from the JSON string returned by the server. To import it in a spreadsheet, one has to create a double array suitable for passing to setValues. How to do this depends on the structure of the object.

The pullJSON gist that you referenced gives a simple model of this process, which works when jsonData is already an array. But different APIs work differently: for example, Stack Exchange API returns an object with several "wrapper" properties, one of which is items that holds an array of objects of interest. So in this case one would loop over the elements of jsonData.items, extracting the properties of each object in the array and pushing them into a double array.

Google-sheets – How to pull data from multiple sheets in Google Spreadsheets 1 and transfer to multiple sheets in Google Spreadsheets 2

Copying data from other spreadsheets is certainly possible with the IMPORTRANGE formula. Example usage form one of my spreadsheets:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/35A4onc5he6l-dy3K_R6zMPd2Nc7o4LjFFEQA8Cx0hCE","April!A2:AH6")

Don't know how to answer about the automation of doing this only at a specified time during the day. Normally IMPORTRANGE works as soon as you open the target spreadsheet.

Best Answer

Related Solutions

Google Sheets – How to Import JSON Using Google Apps Script

Google-sheets – How to pull data from multiple sheets in Google Spreadsheets 1 and transfer to multiple sheets in Google Spreadsheets 2

Related Topic