CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Duncan McDuff
Duncan McDuff

Reputation: 1

App Script, transposing data and appending rows

I have an app script that I am writing that takes a column from a sheet and transposes it into a row. I would like it to transpose to a row below the previous each time it runs. Here is my code so far. Script

function myFunction() {
  // get all the data in the sheet
  var ss  = SpreadsheetApp.openById("1h3-MYEhXI57yuBrP9yakkGXssRBFtdNTNqXKHfBsnEI").getSheetByName("TGImport");
  var range = ss.getRange("L2:L57");
  var values = range.getValues();
  var ss2 = SpreadsheetApp.openById("1IO6jye8d4jv4rJadUAUZ4gwUX6r90ccYQ6FDITuVdnE").getSheetByName("Depletion Record");
  var n = 2
  var date = new Date();
  // transpose it & write it out
  ss2.getRange(n,2,values[0].length,values.length)
    .setValues(Object.keys(values[0]).map ( function (columnNumber) {
      return values.map( function (row) {
        return row[columnNumber];
      });
    }));
  ss2.getRange(n,1).setValue(date)

}

My idea was to have a variable "n" as the row coordinate and just add 1 to it at the end of the function but that doesn't seem to work. Any advice would be nice. Also I am a beginner so go easy on the lingo, haha. Thanks.

Upvotes: 0

Views: 240

Answers (2)

TheMaster
TheMaster

Reputation: 50462

Use getLastRow() to determine the last row (the previous n):

var n = ss2.getLastRow() + 1;

Alternatively, You can store previous n in PropertiesService

Upvotes: 1

Cooper
Cooper

Reputation: 64072

Try this:

function myFunction() {
  var sh1=SpreadsheetApp.openById("1h3-MYEhXI57yuBrP9yakkGXssRBFtdNTNqXKHfBsnEI").getSheetByName("TGImport");
  var rg1=sh1.getRange("L2:L57");
  var vA1=rg1.getValues().map(function(r){return r[0];});
  var sh2=SpreadsheetApp.openById("1IO6jye8d4jv4rJadUAUZ4gwUX6r90ccYQ6FDITuVdnE").getSheetByName("Depletion Record");
  vA1.unshift(new Date());
  sh2.getRange(2,1,1,vA1.length).setValues([vA1]);
}

Upvotes: 1

Related Questions