Google Sheets is known for accessing data from anywhere at any time on any device because of which saving data in spreadsheets is extremely beneficial. But if someone edits the file, then the savings might be lost and the data in the Sheets might be transformed to a new version. This is the reason, we need to save our data in Google Sheets. We can easily save the data in the Google Spreadsheet using Apps Script. On this page, let us understand everything about how to save data in a spreadsheet using PHP using Google Sheets Tips. Read further to find more.
|
Table of Contents |
How To Save Data in Google Sheets?
To save the data in Google Spreadsheet, we need to write a short script that is provided on this page. To get this done, we will assume an example where we want to fetch the published posts of SheetsTips into a Google Spreadsheet and save it in a Google Sheets. To get this done, we can simply use the “IMPORTFEED” function and fetch the data as shown in the image below:
- How to Automatically Send Emails from Google Sheets (Using Appscript)
- How to Convert PDF to Google Sheets: Free Online Conversion
- How to Protect Your Google Spreadsheet Data? (3 Easy Methods)
Creating Save Data Function Using Apps Script to Save Data in Google Sheets
Now we have to save the data and to get this done, we need to write a shortcode in Apps Script. The steps to save the data in the Google Sheets are as shown below:
- 1st Step: Open the Google Sheets on your device,
- 2nd Step: Now on the homepage, in cell A1, import the data which you would like to save. Here we are using Sheetstips blog post data.
- 3rd Step: Now in Cell B1, enter the formula =now() for the timestamp purpose.
- 4th Step: Here you will be shown the time. Now click on the “Extensions” tab and choose “Apps Script” from the drop down menu as shown in the image below.
- 5th Step: In your browser, this will open a new tab. In this window, delete the existing code such as (function increment() { SpreadsheetApp.getActiveSheet().getRange(‘d10’).setValue(SpreadsheetApp.getActiveSheet().getRange(‘d10’).getValue() + 1); })
- 6th Step: Now simply paste the code tabulated below and in the Apps Script window.
| // custom menu function function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu(‘Custom Menu’) .addItem(‘Save Data’,’saveData’) .addToUi(); }// function to save data function saveData() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var url = sheet.getRange(‘Sheet1!A1’).getValue(); var follower_count = sheet.getRange(‘Sheet1!B1’).getValue(); var date = sheet.getRange(‘Sheet1!C1’).getValue(); sheet.appendRow([url,follower_count,date]); } |
- 7th Step: Click on the “Save Project” button.
- 8th Step: Now return to the spreadsheet and refresh your spreadsheet by clicking on F5.
- 9th Step: Here you will see a new tab “Custom Menu” as shown in the image below.
- 10th Step: Click on the “Custom Menu” tab and choose “Save data” from the drop down menu.
- 11th Step: Now the script will start running and the spreadsheet will ask for your permission. Now allow the permission by clicking on the “Continue” button.
- 12th Step: Now provide all the necessary access by clicking on the “Allow” button.
- 13th Step: When it’s finished, it will add a new row beneath your existing entries with a copy of row 1’s data. You may now re-run this at any moment, and it will store a copy of the current settings and timestamp to the same file.
How To Automatically Save Data in Google Sheets?
To automatically save the data in Google Sheets, we need to follow the steps given below:
- 1st Step: Return to your script editor.
- 2nd Step: Select Edit > Triggers for the current project.
- 3rd Step: The triggers dashboard window appears now. In this window, in the bottom right corner, click + Add Trigger.
- 4th Step: Select the Save Data option from the first drop-down menu.
- 5th Step: Select Head, then Time Driven, and then the time period you are interested in for the remaining drop down selections.
- 6th Step: After some time, you should have historic data in your spreadsheet. You may now need to refresh your browser to reload the sheet.
The above steps will help Google Sheets to save the data automatically.