How can you programmatically format content to paste into Google Spreadsheets?

user_169992 0
Viewed 321

Say you want to copy a CSV like this:

foo,1,"asdf"
bar,2,"fdsa"
baz,3,"helloworld"

You copy it with CMD+C/CTRL+C, then go to Google Spreadsheets and press CMD+V/CTRL+V, and you end up with one cell containing all the content. Not what I was hoping for...

How can you format it using JavaScript and the clipboard so it pastes each row/cell into the proper place in the spreadsheet? I have this to do the copying to clipboard in JavaScript:

const textarea = document.createElement('textarea')
textarea.style.opacity = 0
textarea.style.width = 0
textarea.style.height = 0
textarea.style.position = 'absolute'
textarea.style.bottom = '-100%'
textarea.style.left = '-100%'
textarea.style.margin = 0
document.body.appendChild(textarea)

const copy = function(text){
  textarea.value = text
  textarea.select()
  document.execCommand('copy')
}

document.addEventListener('click', () => {
  copy(
`foo,1,"asdf"
bar,2,"fdsa"
baz,3,"helloworld"`
  )
}

How to format it so the Spreadsheet in Google Spreadsheets formats it as rows and columns properly?

google-sheets copy-paste clipboard javascript
2 Answers

Replace comma with tabs and it should work. This is standard behavior in sheets and also excel.

user_92088870 
var values = [
[
// Cell values ...
],
// Additional rows ...
];
var body = {
values: values
};
gapi.client.sheets.spreadsheets.values.update({
spreadsheetId: spreadsheetId,
range: range,
valueInputOption: valueInputOption,
resource: body
}).then((response) => {
var result = response.result;
console.log(`${result.updatedCells} cells updated.`);
});

This is a method provided in by google sheets api and modify upto your needs here is link https://developers.google.com/sheets/api/guides/values#javascript_1

user_162106470
Related