How do I create an export to Google Sheet button on a web page?-CodePudding

Say I have a page like this:

textarea {width:300px;height:200px}
button {display:block}

<textarea value="f">id,value
2,alpha
3,beta
14,test</textarea>
<button>Open in Google Sheet</button>

I want the user to click the button "Open in Google Sheet" and open the csv as a spreadsheet.

I saw that Google Analytics and some other Google products have this button. But I didn't find 3rdparty webapps have this. Is that possible for me to use it?

CodePudding user response：

I believe your goal is as follows.

From I want the user to click the button "Open in Google Sheet" and open the CSV as a spreadsheet., you want to retrieve the text value from the textarea tab and create a Google Spreadsheet using the text value, and then, want to open the Google Spreadsheet.

In order to achieve your goal, how about the following flow?

Retrieve the text value from the textarea tab.
Send the text value to Web Apps created by Google Apps Script.
At Web Apps, a new Google Spreadsheet is created and the text value is put to the sheet.
In order to open the created Spreadsheet, change the permission of the Spreadsheet. In this case, it is publicly shared as the read-only. This is the sample situation.
Return the URL of the Spreadsheet.

When this flow is reflected in the script, it becomes as follows.

Usage:

1. Create a new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access https://script.new/. In this case, if you are not logged in to Google, the log-in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Sample script.

Please copy and paste the following script to the created Google Apps Script project and save it. This script is used for Web Apps. In this sample, the value is sent as the POST request.

function doPost(e) {
  const csv = Utilities.parseCsv(e.postData.contents);
  const ss = SpreadsheetApp.create("sample");
  ss.getSheets()[0].getRange(1, 1, csv.length, csv[0].length).setValues(csv);
  DriveApp.getFileById(ss.getId()).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  return ContentService.createTextOutput(ss.getUrl());
}

3. Deploy Web Apps.

The detailed information can be seen at the official document.

On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
Please click "Select type" -> "Web App".
Please input the information about the Web App in the fields under "Deployment configuration".
Please select "Me" for "Execute as".
- This is the importance of this workaround.
Please select "Anyone" for "Who has access".
- In this case, the user is not required to use the access token. So please use this as a test case.
- Of course, you can also access to your Web Apps using the access token. Please check this report.
Please click "Deploy" button.
Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.
- When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
- You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

4. Testing.

As the test of this Web Apps, I modified your script as follows. Before you use this script, please set the URL of your Web Apps to url. When you open this HTML and click the button, a new Spreadsheet including the text value in the textarea tab is opened with new window as the read-only.

<textarea id="sampletext" value="f">id,value
2,alpha
3,beta
14,test</textarea>
<button onclick="sample()">Open in Google Sheet</button>

<script>
function sample() {
  const url = "https://script.google.com/macros/s/###/exec"; // Please set the URL of your Web Apps.

  fetch(url, { method: "POST", body: document.getElementById("sampletext").value })
    .then((res) => res.text())
    .then((url) => window.open(url, "_blank"));
}
</script>

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
My proposed script is a simple script. So please modify it for your actual situation.