0

trying to get the cell values from html table and send them to google sheet via apps script:

index.html:

     var values = [];
     $("td").each(function(){
       values.push($(this).text());
     });
      
alert(values);  // i can see the array in the alert.

      google.script.run.addRows(values);
    }

code.gs:

function addRows(values) {
  
  var doc = SpreadsheetApp.openById("id");
  var sheet = doc.getSheetByName('newdata');
  var lastrow = sheet.getLastRow();
   sheet.getRange(lastrow+1, 1, values.length, values[0].length).setValues(values);
}

i got an error in the console uncaught addRows, it seems that the array is not detected on the server side, can anyone help thanks in advance

user3140792
  • 71
  • 1
  • 10
  • Related: https://stackoverflow.com/questions/55650581/transfer-a-html-table-user-input-to-google-sheets/55653038#55653038 – TheMaster Feb 13 '22 at 13:47
  • @TheMaster your solution is perfect! but what about the speed of using sheets API vs direct call function to apps script? each call upload about 100rows - 30 cols values together so is Sheets API faster or slower than direct apps script? appreciate your suggestion. – user3140792 Feb 13 '22 at 18:18
  • Sheets API is faster. You can check benchmarks by @Tanaike. See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details or search his posts here in SO. Having said that, batch all the calls in a single upload. That'll be the fastest way to do this. – TheMaster Feb 13 '22 at 19:34
  • The problem is the array you pass into. It should be 2d array [[1,2,3],[3,4,5]]. Create an array and make a unit test on the addRow() function. You should be able to resolve this function – liquidkat Feb 14 '22 at 02:18

2 Answers2

1

This works well for me

gs:

function showSidebar() {
  const html = HtmlService.createTemplateFromFile('index');
  var evaluatHTML = html.evaluate().setTitle('Sidebar')
  SpreadsheetApp.getUi().showSidebar(evaluatHTML)
};

function addRows(values) {
  Browser.msgBox(values)
}

html:

  <script>
    function ok(){
     var values = [];
     $("td").each(function(){
       values.push($(this).text());
     });
      
      alert(values);  // i can see the array in the alert.

      google.script.run.addRows(values);
    }
  </script>

note that you have to transform the result in a 2D Array, for instance

function addRows(values) {
  var tab = [values]
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  sheet.getRange(1, 1, tab.length, tab[0].length).setValues(tab)
}
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
0
 var values = [];
 $("td").each(function(){
   values.push($(this).text());
 });
 var finalarray =  [[values]]

// this will be a 1 row to append

liquidkat
  • 566
  • 4
  • 12