CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Simon Breton
Simon Breton

Reputation: 2876

Looping over row and copy them on the next sheet

I've a list of rows automatically updated and I would like to copy and save them on daily basis.

I've wrote the following script :

function saveData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var rowcount = sheet.getRange("A1:A").getValues();
  var sheetdef = ss.getSheets()[1];

  for(var i = 0; i < rowcount.length; i++){
  var weeknum = sheet.getRange('Feed!A1').getValue();
  var year = sheet.getRange('Feed!B1').getValue();
  var date = sheet.getRange('Feed!C1').getValue();
  var sessions = sheet.getRange('Feed!D1').getValue();
  sheetdef.appendRow([weeknum,year,date,sessions]);
}}

but as you will see it never stop running, plus it only copy the data from the first row. if I'm able to identify my issue I don't really know how to solve it.

I assume that my rowcount line if wrong since it also count blank row. I also need to update sheet.getRange formula so they take dynamic argument.

thanks !

Upvotes: 0

Views: 63

Answers (2)

Vasim
Vasim

Reputation: 3143

Untested but should work:

    function saveData() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];
      //var rowcount = sheet.getRange("A1:A").getValues();
    var Avals = ss.getRange("A1:A").getValues();
    var rowcount = Avals.filter(String).length; //This will take the last non blank cell from A column
      var sheetdef = ss.getSheets()[1];

//I suggest you define Feed sheet as well
var feedsheet = ss.getSheetsByName("Feed")

      for(var i = 0; i < rowcount.length; i++){
      var weeknum = feedsheet(i,1).getValue(); //<- ith row and first/second column
      var year = feedsheet.getRange(i,2).getValue();
      var date = feedsheet.getRange(i,3).getValue();
      var sessions = feedsheet.getRange(i,4).getValue();
      sheetdef.appendRow([weeknum,year,date,sessions]);
    }}

Upvotes: 1

Simon Breton
Simon Breton

Reputation: 2876

With the help of Vasim, I've come up with this:

// function to save data Tendance sessions
function saveData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var sheetdef = ss.getSheets()[1];
  lastRow = sheet.getLastRow();

  for (i = lastRow; i > 0; i--){
  var weeknum = sheet.getRange(i,1).getValue();
  var year = sheet.getRange(i,2).getValue();
  var date = sheet.getRange(i,3).getValue();
  var sessions = sheet.getRange(i,4).getValue();
  sheetdef.appendRow([weeknum,year,date,sessions]);
}}

Upvotes: 0

Related Questions