insert values from textarea HTML input into a column of google sheets

Question

I'm pulling values from a textarea tag in html and trying to insert them into a column in google sheets in order to make a contact list to use later. The user copy pastes from an excel column a list of phone numbers into the text area input.

(the sheet gets created)

Where am I going wrong

<script>
function createGroup() {
  var number = document.getElementById('group').value;
  var name = document.getElementById('groupName').value;

  google.script.run.withSuccessHandler(groupCreated).newGroup(number, name);
 return false;
}
</script>

This is the code.gs script (server side)

function newGroup(number, name){


  var num = number;
       
      //I need to format them to +155555555 format
  var prefix = "+1";
  var removeDashes = num.replace(/-/g,"");
  var addPrefix = prefix + removeDashes;
  var format = addPrefix.replace(/\n/g," +1");
  var arr = format.split(" ");
  
  var array = arr.slice(0);

   
  var length = arr.length;
  
  var sheetName = name;
  var ss = SpreadsheetApp.openById('xxxxxxxxxxxxxxx')
  var newSheet = ss.insertSheet(sheetName);
  
  var activate = newSheet.activate();
  var range = activate.getRange(1, 1, length);
  range.setValues(array);
  return "Done";
}

Cooper · Accepted Answer · 2020-07-27 17:16:29Z

1

You can do something like this:

This creates the dialog with the textarea in the sidebar.

function sidebardialog(obj) {
  var html='<form><textarea name="text" cols="32" rows="15"></textarea><br /><input type="button" value="Paste" onclick="google.script.run.pasteHere(this.parentNode);" />';
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(html));
}

This function pastes the column of data starting at the current cell and pasting downward. When you click on the Paste button in the sidebar

function pasteHere(obj) {
  const rA=obj.text.split('\n').map(function(r){return [r];});//creates a 2 d column array
  const ss=SpreadsheetApp.getActive();//using the contained spreadsheet
  const sh=ss.getActiveSheet();//the active sheet
  const row=sh.getActiveCell().getRow();
  const col=sh.getActiveCell().getColumn();
  sh.getRange(row,col,rA.length,1).setValues(rA);//pastes all of the data at one time
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('My Tools')
  .addItem('Paste Data', 'sidebardialog')
  .addToUi();
}

edited Jul 27, 2020 at 17:16

answered Jul 27, 2020 at 17:10

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

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

9 Comments

jack Over a year ago

Thanks, I'm working on a web app so I adapted your method and it worked my only issue is it creates a extra cell with just the prefix "+1". I suppose it's because theres a \n after the last cell. Is there a way to get rid of it?

jack Over a year ago

Ok took care of that with rA.pop() Thanks @Cooper!

Cooper Over a year ago

You could use filter method to.

jack Over a year ago

How would I do reverse, if I'm pulling the values from the Spreadsheet, now they're 2D how do I convert it into a long Array

Cooper Over a year ago

If you're pasting them into a text area I think they're no longer a 2D array

|

Collectives™ on Stack Overflow

insert values from textarea HTML input into a column of google sheets

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related