Google Apps Script code to set value in the next blank row

2
votes

I'm trying to simply my life by creating a Google Apps Script that makes a copy of another template sheet in my drive and then paste the URL and the name of that new sheet in the original sheet I run the script from.

The script I've made below does just that. However, every time the script is ran, the new URL value is always pasted in "A12". How can I make the script paste that value to the next cell down?

function saveAsSpreadsheet£(){ 
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var ss = sheet.getSheets()[0];
  var range = ss.getRange("I6");
  var values = ss.getRange("A12");
  var data = range.getValue();
  var destFolder = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxx");
  var makecopyurl = DriveApp.getFileById(sheet.getId()).makeCopy(data + " Management Workbook", destFolder).getUrl();

  var npo = '=hyperlink("' + (makecopyurl) + '";"' + data  + '")';
  values.setValue(npo)}

Here's a link to an example spreadsheet.

2
google-apps-scriptgoogle-sheets
You should replace A12 inside ss.getRange("A12") with the cell where you wish to paste the new link. - Amit Agarwal

2 Answers

2
votes

If the "next cell down" is the just below the last row, you could use getLastRow and use getRange(row,column):

replace

var values = ss.getRange("A12");

by

var lastRow = ss.getLastRow();
var values = ss.getRange(lastRow + 1, 1);
2
votes

You could use appendRow()

This method looks for the last row containing data and then appends to the next one. 

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

// Appends a new row with 3 columns to the bottom of the
// spreadsheet containing the values in the array
sheet.appendRow(["a man", "a plan", "panama"]);