send html table data to google sheet via apps-script

Question

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

@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
– user3140792, Commented Feb 13, 2022 at 18:18
Sheets API is faster. You can check benchmarks by @Tanaike. See tag info page 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
– TheMaster, Commented Feb 13, 2022 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
– liquidkat, Commented Feb 14, 2022 at 2:18

Mike Steelson · Accepted Answer · 2022-02-13 13:31:46Z

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)
}

answered Feb 13, 2022 at 13:31

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

liquidkat · Accepted Answer · 2022-02-14 02:23:02Z

0

 var values = [];
 $("td").each(function(){
   values.push($(this).text());
 });
 var finalarray =  [[values]]

// this will be a 1 row to append

answered Feb 14, 2022 at 2:23

liquidkat

5864 silver badges12 bronze badges

Collectives™ on Stack Overflow

send html table data to google sheet via apps-script

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related