0

So I have a script that updates one cell, the function is triggered weekly:

function updateCell() {
 SpreadsheetApp.getActiveSheet().getRange('a2').setValue('=Index(ImportData("URL HERE"),1,1)');
}

The thing is, this function only updates A2 (as it is set). What I want to do is update 3 cells and then go to the next line. Here is my FNX:

function RecordToSheet() {
  /*Record Date & Time*/
 SpreadsheetApp.getActiveSheet().getRange('a2').setValue('=NOW()');
  /*Record 1*/
 SpreadsheetApp.getActiveSheet().getRange('b2').setValue('=Index(ImportData("URL1"),1,1)');
  /*Record 2*/
 SpreadsheetApp.getActiveSheet().getRange('c2').setValue('=Index(ImportData("URL2"),1,1)');
}

As you see, the "getRange" is set to a2, b2, c2 (since a1, b1, and c1, is occupied by text). I need the script to "go to the next line" when it adds a value instead of change the current value. How would I do this?

1

You can use appendRow() function to append the new values to the first empty row

Your code will look like this:

function RecordToSheet() {
 // make a data array with your row data 
 data = ['=NOW()','=Index(ImportData("URL1"),1,1)','=Index(ImportData("URL2"),1,1)']
 // Append the data array
 SpreadsheetApp.getActiveSheet().appendRow(data)
}
1

When we want to add formulas to a spreadsheet we should use setFormula() / setFormulas(), not setValue() / setValues().

Regarding going to the next cell, instead of A1 notation you could use the other forms like getRange(row, column)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.