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?
Replace comma with tabs and it should work. This is standard behavior in sheets and also excel.
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