1

the spreadsheet in question - https://docs.google.com/spreadsheets/d/1t50ieV8dRvvyfsYVaxf_ZthToA-60dSW6xpjBNvVvA4/edit?usp=sharing

The value in A5 indicates which sheet the data should be submitted to.

I'm new to scripting, and have found a simple bit of code that looks like it will do the trick, but because I don't understand part of it, I'm unable to adapt it to my sheet.

function submitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Tool Life");
  var range = sheet.getRange(1,5)
  var data = range.getValue()
  var datasheet = ss.getSheetByName(data);
  
  //Input Values
  var values = [[sheet.getRange("B5").getValue(),   //tool
                 sheet.getRange("C5").getValue(),   //reason
                 sheet.getRange("D5").getValue(),   //replaced
                 sheet.getRange("E5").getValue(),   //current
                 sheet.getRange("F5").getValue(),   //new
                 sheet.getRange("G5").getValue(),   //from length
                 sheet.getRange("H5").getValue(),   //to length
                 sheet.getRange("I5").getValue(),   //from diameter
                 sheet.getRange("J5").getValue(),   //to diameter
                 sheet.getRange("K5").getValue(),   //where used
                 sheet.getRange("L5").getValue(),   //flagged
                 sheet.getRange("M5").getValue(),   //set to measure
                 sheet.getRange("N5").getValue(),   //name
                 sheet.getRange("O5").getValue(),   //date
                 sheet.getRange("P5").getValue()]]; //comments
                 
  
  datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 15).setValues(values);
}

I don't understand what the numbers are for on the last line of code. I'm assuming the 15 is for either the number of columns, or number of entries.

I'm taking a guess that I can use the variable "data" in this line: var datasheet = ss.getSheetByName(data); but because it doesn't work, I can't play around with it.

Erik Stenerud
  • 35
  • 4

1 Answers1

2

Explanation:

You are very close.

  • According to the spreadsheet that you provided, you want to get cell A5 of the Tool Life sheet. You are calling sheet.getRange(1,5) but that gives cell E1. You should use sheet.getRange(5,1) or sheet.getRange('A5') instead to get the correct cell.

  • It is always a good idea to reference to the length of values instead of hardcopying the ranges. Use that instead:

    datasheet.getRange(datasheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);

Solution:

function submitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Tool Life");
  var range = sheet.getRange(5,1);
  var data = range.getValue();
  var datasheet = ss.getSheetByName(data);
  
  //Input Values
  var values = [[sheet.getRange("B5").getValue(),   //tool
                 sheet.getRange("C5").getValue(),   //reason
                 sheet.getRange("D5").getValue(),   //replaced
                 sheet.getRange("E5").getValue(),   //current
                 sheet.getRange("F5").getValue(),   //new
                 sheet.getRange("G5").getValue(),   //from length
                 sheet.getRange("H5").getValue(),   //to length
                 sheet.getRange("I5").getValue(),   //from diameter
                 sheet.getRange("J5").getValue(),   //to diameter
                 sheet.getRange("K5").getValue(),   //where used
                 sheet.getRange("L5").getValue(),   //flagged
                 sheet.getRange("M5").getValue(),   //set to measure
                 sheet.getRange("N5").getValue(),   //name
                 sheet.getRange("O5").getValue(),   //date
                 sheet.getRange("P5").getValue()]]; //comments
                 
  
  datasheet.getRange(datasheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}

References:

Marios
  • 26,333
  • 8
  • 32
  • 52