javascriptgoogle-sheetsgoogle-apigoogle-sheets-apigoogle-data-api

Is it possible to append to the next column horizontally in google sheets?

I currently have this script. I works fine, however, I would prefer if it appended to the next column horizontally each time a choose to append. Right now it appends after the last row. Please be very specific about what needs changing. Thanks!

function myFunction() {
  var ss = SpreadsheetApp.openById("1aACg8M89LLxILIvUqWT2ZnrykF8e3BkIiamj2NhKrmc")
  var sheet = ss.getSheetByName("Framsida");
  var data = sheet.getRange(2, 3, 25, 1).getValues();
  for (var i = 0; i < data.length; i++) {
    Logger.log(data[i][0]);
    Logger.log(data[i][1]);
  }
  var row= sheet.getLastRow();
 Logger.log(row);
 Logger.log(data);
   var temp = data
     for (i in temp){
       var write = temp[i]
       if (row >=10) {ss.getSheetByName("Archive").appendRow(write)};

     } 
   ss.getSheetByName("Framsida").getRange(2, 3, 25, 1).clear();

}
Solution

  • You want to retrieve the values from the cells of "C2:C26" on the sheet "Framsida", and want to put the values to the next column of the last column on the sheet "Archive".

    In order to retrieve the last column of the sheet, please use getLastColumn(). Here, I would like to propose 2 patterns for achieving your goal.

    Pattern 1:

    In this pattern, getValues and setValues are used.

    function myFunction() {
  var ss = SpreadsheetApp.openById("1aACg8M89LLxILIvUqWT2ZnrykF8e3BkIiamj2NhKrmc")
  var srcSheet = ss.getSheetByName("Framsida"); // <--- Modified
  var srcRange = srcSheet.getRange(2, 3, 25, 1);
  var data = srcRange.getValues();
  
  var dstSheet = ss.getSheetByName("Archive");
  dstSheet.getRange(2, dstSheet.getLastColumn() + 1, data.length, 1).setValues(data);
  
  srcRange.clear();
}

    Pattern 2:

    In this pattern, copyTo is used.

    function myFunction() {
  var ss = SpreadsheetApp.openById("1aACg8M89LLxILIvUqWT2ZnrykF8e3BkIiamj2NhKrmc")
  var srcSheet = ss.getSheetByName("Framsida"); // <--- Modified
  var srcRange = srcSheet.getRange(2, 3, 25, 1);
  
  var dstSheet = ss.getSheetByName("Archive");
  srcRange.copyTo(dstSheet.getRange(2, dstSheet.getLastColumn() + 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES);
  
  srcRange.clear();
}

    References: