In this post, you’ll learn how to save the data in Google Sheets with timestamps, so that you have a record of the data at set time intervals (e.g. once a day or once a week).
We use Apps Script to save the data and set it to run automatically in the background.
Creating a Save Data function with Google Apps Script
Here’s our scenario: imagine we’re tracking a social media channel, for example, the number of followers of a Reddit group.
Maybe we enter the data manually, or better collect it automatically via Zapier or even via the API with Apps Script.
In other words, it changes daily and we want to record a snapshot in time.
First, let’s add a custom menu to Google Sheets which saves the current number of followers plus a timestamp in the rows below.
Second, we’ll look at setting up an automatic trigger to do this for us at set intervals (e.g. once per day).
The steps in detail then:
- Assume you have data in A1 and B1, put the formula
=now()into cell C1, to give us our timestamp. Our spreadsheet should look like this: - Type headings into row 4, as shown in this image:
- Next go to Tools > Script editor…
- This opens a new tab in your browser. Remove the existing code in the main window (the bit of code:
function myFunction() { }). - Paste the following code into the window:
- Hit save.
- Return to your spreadsheet window.
- Reload your spreadsheet (refresh your browser) and you should see a new menu: Custom Menu > Save Data item
- Run this. First time it’ll ask you for permission so click Allow.
- When it runs, it will append a new row beneath your existing entries with a copy of the data from row 1.
- You can now re-run this whenever you want and it’ll save a copy of the current values and timestamp below.
// custom menu function
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Save Data','saveData')
.addToUi();
}
// function to save data
function saveData() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const url = sheet.getRange('Sheet1!A1').getValue();
const follower_count = sheet.getRange('Sheet1!B1').getValue();
const date = sheet.getRange('Sheet1!C1').getValue();
sheet.appendRow([url,follower_count,date]);
}
This adds two functions to our spreadsheet, one to save the data and the other to create a new custom menu so we can run our save data function from within our spreadsheet.
Great!
The next step is to automate this saving data function to run at set intervals.
Save Data In Google Sheets Automatically
We’ll use an installable trigger to save timestamped data in Google Sheets automatically.
Steps for automatic data saving:
- Go to back to your script editor (Tools > Script editor… if you closed that tab).
- Click on Edit > Current project’s triggers
- This brings up the triggers dashboard window. In this window, click on + Add Trigger in bottom right corner
- In the first drop down, select the Save Data function
- For the remaining drop down options, select Head, then Time Driven and then the period you’re interested in.
- Voila! Come back after a while and you should have historic data saved in your spreadsheet (you may need to reload the sheet by refreshing your browser). It really is that easy.
Any comments or questions, feel free to leave them in the comments below!
Hi, Ben!!
First that all, thank you for everything, this is so amazing!
I want to ask you about Twitter code crash often, do you have any idea how to solve it, or why is happening? Both of them are having problems.
It works normally but after a few minutes shows #VALUE message.
Best regards,
Thanks Luisa! Glad to be of help.
The IMPORT functions can be temperamental and are affected by things like caching and changes to the XML/HTML structure of the original URL. Try adding (or removing) a final “/” at the end of the URL as Google will treat this as a new, different URL to go and fetch, so the caching is not an issue. I’ve found this sometimes fixes the issue.
Also, in your function you could wrap the import XML function in an IFERROR function to have a custom error messages instead of a
#N/A. For example, my function would look like this (assuming the Twitter URL is in cell A1):=iferror(query(IMPORTXML(A1,"//a[@data-nav='followers']"),"select Col2"),"Not available at this time")In my other web scraping article, I list two Twitter import methods so you try both, and then pick the one that works using an IF function for example.
Ben,
Thank you again. I add a “/” at the end of the URL, and it works. I will let you know if crash it again. You’re awesome, I am so into this (code in Google SpredSheets) after I read your blogs.
Looking for learning more, do you know some free resources that I can look?
Thanks!
Great!
I find these sites pretty useful for Google Sheets work:
Official Google Sheets documentation
Google Sheets forums
For more developer orientated work inside Google sheets using App Script, check out these ones:
Introduction to Apps Scripts
App Script documentation
Digital Inspiration – often has Google Sheets articles e.g. http://www.labnol.org/internet/google-scripts/28281/
Also, keep an eye out here, lots more content on Google Sheets coming this way! I’m creating a Google Sheets dashboard course and hoping to launch in a couple of months.
Thanks,
Let me know if you need help. I will work for free to learn more about this project. I will take a look and make it my goal for this year.
Thank you for the inspiration and the good content.
Hey Ben. first thank you for all the help you give us.
I have a question. How to I save data in one sheet form to another sheet in a different sheet in another work book ?Currently I can do that but on the same workbook. Here is the script code am using.
//var SPREADSHEET_NAME = “Data”;
//var SEARCH_COL_IDX = 0;
//var RETURN_COL_IDX = 0;
//
// Save Data
function submitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName(“Form1”); //Form Sheet
var datasheet = ss.getSheetByName(“Data”); //Data Sheet
//Input Values
var values = [[formSS.getRange(“C8”).getValue(),
formSS.getRange(“C10”).getValue(),
formSS.getRange(“C12”).getValue(),
formSS.getRange(“E8”).getValue(),
formSS.getRange(“E10”).getValue(),
formSS.getRange(“C14”).getValue(),
formSS.getRange(“C20”).getValue()]];
// Save New Data
datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 7).setValues(values);
SpreadsheetApp.getUi().alert(‘ “New Data Saved – Emp #’ + formSS.getRange(“C8”).getValue() +’ “‘);
formSS.getRange(“C8”).clear();
formSS.getRange(“C10”).clear();
formSS.getRange(“C12”).clear();
formSS.getRange(“E8”).clear();
formSS.getRange(“E10”).clear();
formSS.getRange(“C14”).clear();
formSS.getRange(“C20”).clear();
}
hey ben!
i want a sheet that will track the editing history of my first sheet can you help me this i need it urgently for my school project
Hi Ben,
Thank you for all of your posts and examples. I am glad I found your site and have already subscribed so I don’t miss anything new.
I found this post while trying to figure out how to save data in 3 different tabs of the same workbook by only entering the data once.
I will enter the following data: idNum, firstName, lastName, teacher, className, and classTime.
Then, based on the entry in className, the idNum, firstName, and lastName would be added to the end of the grade book roster for that class tab, classAttendance tab, and then all of the data entered would be saved and sorted by idNum in the Students tab.
Am I being to ambitious with the scope of this post? I am pretty new to Google Apps Script so I am not entirely sure what is and isn’t possible to do.
Hey Jason!
This is entirely feasible in Apps Script and it’s good to be ambitious!
The code in my blog post needs a little modification though. You need to get the data from the input sheet, which will be in an array, and loop over it to identify the class name and match that to the different tab names. Each loop you can then paste the data into the matching tabs.
Here’s a very quick example, feel free to make a copy so you can edit it: https://docs.google.com/spreadsheets/d/1kIJUV3RBmRZtGQMdaEt3AZOkHn1vQ4w57YSp39cgBVE/edit?usp=sharing
And this is the code:
// function to add custom menu to your spreadsheet// Need to run this from the script editor menu first time (select this function and hit play button above)
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Save to Tabs', 'saveToTabs')
.addToUi();
}
// function to save data to different tabs
function saveToTabs() {
// get input sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Input');
// get data in input sheet
var data = sheet.getRange(2,1,sheet.getLastRow()-1,6).getValues();
// loop over array of input data and log each element to the correct tab
// where the tab name matches the class name in the input sheet
for each (elem in data) {
// select class name from array
var class = ss.getSheetByName(elem[4]);
// paste data to specific tab where class name in array matches tab name
class.getRange(2,1,1,6).setValues([elem]);
}
}
Hope that helps!
Cheers,
Ben
Hi Ben,
Thanks for your response even though this is an old post. I think I am really close to finishing this script thanks to your guidance. I have modified your script to do a little more and now have come to a roadblock because I don’t know if appendRow() is the correct method for my case.
I can copy all of the input values, store them, and send them to the correct tabs. The problem I am having is that I only want to save the first 3 columns into the class tabs because starting in column “D” there are formulas for grade calculations.
Here is my Spreadsheet: https://docs.google.com/spreadsheets/d/10IROWMjccVJJednKFXIFcHpU2kjWEvOKAURKcdZIPSE/edit#gid=0
I am using appendRow(), but since there are formulas providing temporary blanks in column D, it adds a row after my last row so it can be a fully blank row. Is there a way to restrict appendRow() to only look at the first 3 columns? If not, is there another method to add it to the end of a list?
Thank you again for all that you do.
Hey Jason,
You can use
getRange()and thensetValues()to restrict to the 3 columns only. The tricky part is working out the last row in the first column to tell apps script where to put the new data, even if there are formulas in column D that go much further down (meaning we can’t use the handygetLastRow()method). The new stuff is bolded. Note it looked to me like classAttn was missing from the original appendRow in the “Students” sheet, so I added that into the array.I modified your saveData function (for the attendance sheet only) which should give enough to customize to your own needs:
// function to save datafunction saveData() {
// starts with active sheet for data entry
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Students");
// collects values in data entry row
var idNum = sheet.getRange('Students!A1').getValue();
var firstName = sheet.getRange('Students!B1').getValue();
var lastName = sheet.getRange('Students!C1').getValue();
var teacher = sheet.getRange('Students!D1').getValue();
var className = sheet.getRange('Students!E1').getValue();
var classTime = sheet.getRange('Students!F1').getValue();
var classAttn = sheet.getRange('Students!G1').getValue();
// adds all collected data to end of active sheet tab
sheet.appendRow([idNum,firstName,lastName,teacher,className,classTime,classAttn]);
// identifies and adds data to appropriate class
// this adds to the bottom of entire sheet because columns D through H have functions
// need to figure out how to add it to the end of the list in first 3 columns only
var classSheet = ss.getSheetByName(className);
classSheet.appendRow([idNum,firstName,lastName]);
// identifies and adds data to class attendance
var attendance = ss.getSheetByName(classAttn);
var aVals = attendance.getRange("A1:A").getValues();
var aLastRow = aVals.filter(String).length;
Logger.log(aLastRow);
var newAttData = [[idNum,firstName,lastName]];
Logger.log(newAttData);
attendance.getRange(aLastRow + 2,1,1,3).setValues(newAttData);
}
Hope that helps!
Cheers,
Ben
Hi Ben,
Great post indeed, thanks a lot!
I’m importing an RSS feed and wonder if you know if there’s any way to append the rows directly on import to have them saved in real-time as the RSS-feed updates?
Thanks,
Jonas
Hi Ben!
This is amazing! Seriously thanks for such a helpful post and especially for getting back to people so soon about their questions even nearly a year after your post.
I want to use the save data function with the time driven trigger for a project, but mine is a little different:
I’m using the google analytics ad-on in google sheets to run a report to determine accumulated page views for specific urls over time. I’m turning everything into a pivot table then pulling that data in the pivot table to a new sheet, into a specific tab (multiple different tabs depending on what URLs i’m looking for). I’ve also set this report to automatically run once a week.
Now this is where the save data function and time trigger come into play: I want to have the GA report run once a week, lets say Monday at 5am, I also want the save data function to run on Monday each week but at 7am, and have the save data to copy and paste those pulled page views into a new column for that new week.
So with pulling data from my GA sheet into another sheet (using a vLookup and importRange), how could I best write that in my script? Especially since i’ll be adding new urls usually on a weekly basis.
Hopefully that made as much sense as possible.
-Bradley
Hey Bradley,
Sorry for slow reply, just catching up on work after a being sick.
You can select different sheets in Apps Script, using
getSheetByName("SHEET NAME HERE"), which would save you needing to use any vlookups. So, you’d pull your GA data into one sheet and set that to run automatically. Then an hour or two later, your other function runs automatically, selects the sheet with the GA data in, selects the GA data and then copies that data into your other sheets that you’ve selected in your code.The line to select different sheets would be something like this:
var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet1 = ss.getSheetByName("Sheet1");
var sheet2 = ss.getSheetByName("Sheet2");
And you can also select different spreadsheets if you want.
Hope that helps!
Ben
Hello Ben,
Your last comment answering Bradley’s problem is precisely what I’m trying to do, select different spreadsheets.
How would the code be? I know of getActiveSpreadsheet(), as you are using it here above, but I couldn’t find anything like get SpreadsheetById() or similar. I was looking in https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app, but nope. So I’m pretty happy you say it’s feasible…
Thank you, Cédric
I am getting the same issue, the code .getSheetByName and .getSheetId both do not seem to work or be available….
Hi Ben,
cool examples!
Do you have an example blog of how one can implement Tasks API to a Google Forms, Google Spreadsheets and Google Calendars (team calendar)?
I basically want my team to goto team site page on Google Sites, open page containing Google form for entering their project details and tasks. This information gets saved to a backend Google Spreadsheet (with me), as well as shows up on the Team calendar (also embedded in a page in Google sites). Once they complete their tasks, they can just visit the team calendar and tick their tasks to indicate finished. This logs their finished status directly in the backend Spreadsheet against their individual tasks. This is for a small team of 4 people only.
any help to get me started with instructions/code would be most welcome.
Thanks.
Hi, this article has been very helpful and easy to follow, however, there is a modification I would like to make in the data saving feature. Instead of appending the newest saved values at the end of the list, I would like to insert the newest saved values at the top of the list. In the example you show, the newest values would go into cells A5, B5, C5 with the older values moving down. Can this done? If so could you please demonstrate how? Thank you!
Hi Reed,
You can do as follows:
Remove the line of code that says
sheet.appendRow([url,follower_count,date]);and replace it with these new lines:
// Get the current datavar currentData = sheet.getRange(5,1,sheet.getLastRow(),3).getValues();
// Move it all down one row
sheet.getRange(6,1,sheet.getLastRow() + 1,3).setValues(currentData);
// Clear the top line and paste in the new data
sheet.getRange(5,1,1,3).clear();
sheet.getRange(5,1,1,3).setValues([url,follower_count,date]);
I haven’t tested this code mind you, but it should give you enough to get this working.
Cheers,
Ben
Hi ben,
I am prey new to google sheet even. I want to append data from different cells of sheet1 and put it in row in new sheet2. Can can I do this using GAS. Currently I am doing this job manually.
Thanks
This post was very helpful.
Currently, I am using a workbook with 5 different tabs in it (Monday-Friday) and entering data in cells (A1:I51) for each sheet.
I would:
1. Like to save the workbook as whole (all 5 sheets at once) and
2. Make a copy of the workbook when it saves so I can clear the original to use the following week. I figure this may be a lot to ask but I cannot figure out how to do this otherwise and this was by far the most recent and helpful post I could find. Any help would be appreciated. Thanks!
Hello Ben,
This looks good. I will try to create this also for my sheet.
I have a few importxml formula’s but they don’t seem to update.
=IMPORTXML("http://coinmarketcap.com/currencies/bitcoin/?paratmer=1","//span[@class='text-large']")=IMPORTXML("http://coinmarketcap.com/currencies/bitcoin/?paratmer=1","//span[@class='text-large']")
These formula’s are in a cell. How can I set the values from the result from the IMPORTXML to a specific cell. For example first formula on cell b2 and second formula on b3.
John
Hey John,
I see what you mean about not updating. Not sure why, as other IMPORT formulas I run update ok. The two formulas you’ve shared look the same to me?
I would change the
"//span[@class='text-large']"to"//span[@id='quote_price']"to ensure you get the correct price, in case the text-large class is used elsewhere on the website.Also, if you move the URL into it’s own cell, e.g. A1, and then refer to that with the formula it’s easier to make changes. You can actually drop the “?paratmer=1” too. So you could use:
=IMPORTXML(A1,"//span[@id='quote_price']")You can then add or drop the final “/” of the url
http://coinmarketcap.com/currencies/bitcointo trigger a refresh…Cheers,
Ben
Hello Ben,
Thanks for great tip. Works like charm.
Sorry it was indeed a double formula.
But it works.
Thanks for your help.
John
Great! Happy to help. Yes, seems to be updating ok in my example too now. Cheers.
Hello Ben,
I have one question. Where did you find the quote_price in the xml?
=IMPORTXML(A1,”//span[@id=’quote_price’]”)
Is there a function to see al the different id’s?
I want to use the api to get a Euro price.
https://api.coinmarketcap.com/v1/ticker/bitcoin/?convert=EUR
And use the price_eur id
Kind regards,
John Slegers
Hi John,
I used the Chrome Developer Tools to look at the underlying HTML/CSS for the website, as in this image:
On a Mac, you can access the Chrome Developer Tools with Command + Option + I.
However, if you need to use the API to get the EURO price, then you’re going to need Apps Script (intro to APIs here). Theoretically it should be possible, how difficult depends on how open the API is and how good the documentation is.
Cheers,
Ben
I modified the script slightly, just to add a few more columns for other social media sites, and now nothing happens when it runs.
Any idea why??
// 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 date = sheet.getRange(‘Sheet1!A1’).getValue();
var facebook = sheet.getRange(‘Sheet1!B1’).getValue();
var twitter = sheet.getRange(‘Sheet1!C1’).getValue();
var instagram = sheet.getRange(‘Sheet1!D1’).getValue();
var youtube = sheet.getRange(‘Sheet1!E1’).getValue();
var youtubeviews = sheet.getRange(‘Sheet1!F1’).getValue();
sheet.appendRow([date,facebook,twitter,instagram,youtube,youtubeviews]);
}
I have the same issue.
Code looks good but does not return any data.
Anyway…
Ben Great tutorial! I hope you will continue such a great job 🙂
Kind regards
Przemek
Hey Przemek,
Hmm
Hey Sam, must have missed this comment originally, sorry ’bout that. You might need to run your onOpen function again from the Script Editor and then try your menu again to run the main sheet. It’s probably just running the old function each time at the moment.
Hope that helps!
Ben
Hi Ben!
Great post.
Is it possible to disable the core autosave of google spreadsheet?
The scenario is that users work in this spreadsheet that is shared and anyone with the link can edit but users are changing their input and ouput.
Best Chris
thank you for you great tutorial. it works fine to me, fetching data from worldcoinindex.com (I scrap the btc cap, alt cap and mrkt cap).
though strangely, the value are never exactly matching (first problem) and when saved with the menu they are not refreshing
the cell function is (for market cap) :
=IMPORTXML(A1;”/html/body/div[1]/div/div[8]/span[2]”)
and the script is :
// 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(‘Feuille 1!A1’).getValue();
var date= sheet.getRange(‘Feuille 1!B1’).getValue();
var btccap = sheet.getRange(‘Feuille 1!C1’).getValue();
var altcap = sheet.getRange(‘Feuille 1!D1’).getValue();
var mktcap = sheet.getRange(‘Feuille 1!E1’).getValue();
sheet.appendRow([url,date, btccap, altcap, mktcap]);
}
Would you have any idea for it to refresh properly the data and save them correctly, please?
Hi,
I’m using the codes for Instagram, but everytime in google spreadsheet it says Error, how is that possible?
Thank you in advance.
Herman
Hey Herman,
These import formulas are sometimes volatile and stop working. I post the most up-to-date versions in this post: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#instagram
Cheers,
Ben
Hi Ben,
I am trying to save view data to google sheet from google app script’s client side on every control filter change. Is there any function/way to do so.
Thanks
Sumit
Hey Sumit,
I’m not quite sure what you’re after but I don’t think you can do what you’re trying to do. The onEdit() trigger can be used to run a function each time a change is made to values in your spreadsheet, but it isn’t triggered by filters.
Cheers,
Ben
Just curious, how does it know to start appending rows at the 5th row? Is it because headers were placed there?
Exactly. The line that writes the data into the spreadsheet:
sheet.appendRow([url,follower_count,date]);will take the first blank row, which happens to be the 5th row the first time it’s run.
Makes sense, thanks Ben! These tutorials are great.
Instead of appending the newest saved values at the end of the list, I would like to insert the newest saved values at the Active range of the list. In the example the newest values would go into cells B5, C5, D5 with the older values moving down. Can this done? If so could you please demonstrate how? Thank you!
Yes, this can be done! Try this script for the save data function:
function saveData() {var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
sheet.insertRowsBefore(5, 1);
var url = sheet.getRange('Sheet1!A1').getValue();
var follower_count = sheet.getRange('Sheet1!B1').getValue();
var date = sheet.getRange('Sheet1!C1').getValue();
sheet.getRange(5,1,1,3).setValues([[url,follower_count,date]]);
}
It’ll insert a new row at position 5, then paste the new data in there.
Cheers,
Ben
Thank you man, this is awsome, but I would like to replace:
var sheet = ss.getSheets()[0];
by:
var sheet = ss.getActiveSheet();
Hi Ben,
Thank you very much for coming up with such a useful script. I am very new to script writing and currently trying to revise your provided script to suit my needs. May I check is it possible to save the selected fields input into a specific spreadsheet? How should this “sheet.appendRow([WIP,follower_count,date]);” be revised to allow appending it to a specific spreadsheet’s next empty row?
Hi Vicky,
To append to a specific spreadsheet, you’ll want to change this line:
var ss = SpreadsheetApp.getActiveSpreadsheet();to this:
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc1234567/edit');where you put in the url of the spreadsheet you want to paste values too.
See more here: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyurlurl
Hope that helps!
Ben
Hi Ben, if i am intending to append it to a specific tab of the same spreadsheet, do I change this line:
var ss = SpreadsheetApp.getActiveSpreadsheet();
to this:
var ss = ss.getSheetByName(‘tab name’)?
I have tried doing this and it is giving me an error. Hope that you can help me with it.
Hi Ben, if i am intending to append it to a specific tab of the same spreadsheet, do I change this line:
var ss = SpreadsheetApp.getActiveSpreadsheet();
to this:
var ss = ss.getSheetByName(‘tab name’)?
I have tried doing this and it is giving me an error. Hope that you can help me with it.
Try this:
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('tab name');That should work for you! See more here: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename
Cheers,
Ben
thank you ben it really helps me allot but i am going to do some modification in the current example im gonna use it in my purchase order using same concept thought my problem is that i cannot save the data in a multiple row the current sample only work on single row when i click save
Hi Ben!
My case:
I am using Google Forms with Google Sheets as base. In Google Sheets I have an ImportRange function from an different Sheet.
The add on “Form publisher” copies the base Sheet, but then the Import range data is gone, because I hafto go in to the new sheet and click “allow accsess”.
I need a script to copy the intire ImportRange area (A1:G1600) with intervalls. And the area needs to be overwritten so the area stays in the same position (using the data for further calculation).
// 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.getSheetByName(“data”);
So far so good… but:
var nr = sheet.getRange(‘data!A1:G1600’).getValue();
sheet.appendRow([nr]);
}
and:
overwrite needs to be added.
Greetings from Norway
I got this to work:
function copyRange() {
var sss = SpreadsheetApp.openById(‘1VuXAS5BSr_wqbWJ1dS-7ee6aFo2WonQc-TKdVYEfWl0’); //replace with source ID
var ss = sss.getSheetByName(‘data’); //replace with source Sheet tab name
var range = ss.getRange(‘A2:F10’); //assign the range you want to copy
var data = range.getValues();
var tss = SpreadsheetApp.openById(‘1VuXAS5BSr_wqbWJ1dS-7ee6aFo2WonQc-TKdVYEfWl0’); //replace with destination ID
var ts = tss.getSheetByName(‘ark’); //replace with destination Sheet tab name
ts.getRange(ts.getLastRow()+1, 1,9,6).setValues(data); //you will need to define the size of the copied data see getRange()
}
Just need it to be cleared first 😉
Ref: [https://stackoverflow.com/questions/12291549/copy-a-range-from-one-spreadsheet-to-another]
function removeRange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(‘ark’);
sheet.getRange(4,1,12,6).clear();
}
//VI MÅ FÅ DE TIL Å GÅ SAMTIDIG
// rydd og kopier
function copyRange() {
var sss = SpreadsheetApp.openById(‘1VuXAS5BSr_wqbWJ1dS-7ee6aFo2WonQc-TKdVYEfWl0’); //replace with source ID
var ss = sss.getSheetByName(‘data’); //replace with source Sheet tab name
var range = ss.getRange(‘A2:F10’); //assign the range you want to copy
var data = range.getValues();
var tss = SpreadsheetApp.openById(‘1VuXAS5BSr_wqbWJ1dS-7ee6aFo2WonQc-TKdVYEfWl0’); //replace with destination ID
var ts = tss.getSheetByName(‘ark’); //replace with destination Sheet tab name
ts.getRange(ts.getLastRow()+1, 1,9,6).setValues(data); //you will need to define the size of the copied data see getRange()
Getting theese two functions together was not perfekt, I need a delay between them.
Any ideas?
Yoo Ben,
Let me say… as someone who runs a blog + a youtube channel, the fact that you respond to your comments… in SUCH detail, is absolutely INCREDIBLE. Big ups man.
Love the idea of this above. We import data from YT, and want to save a copy of it periodically to do analysis. Don’t want to take traffic from your great site, so feel free to delete this comment… but I also found this helpful (as it provided a bit of flexibility in where I could paste the data + allowed me to copy a range of data): https://productforums.google.com/forum/#!msg/docs/BGw5mW1s18Y/pI_Je0JTLQAJ
K
Thanks Kyle!
Hi Ben,
Great tool, thanks a lot. I only would like to save data to a new row when the content of the cell has changed. Any chance you can help me with the code for this?
Hi Ben,
Thanks for all the great content to help with Google Sheets. I’m currently trying to use your saveData Function to create a database. I have one sheet (Data) to feed my data dynamically (each week) and one sheet to store all the data from each week (Database). I’ve adapted the original script for my needs using your reply to a previous comment. This is what I have for now :
// custom menu function
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Save Data’,’saveData’)
.addToUi();
}
// function to save data
function saveData() {
// starts with active sheet for data entry
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(“Data”);
// collects values in data entry row
var url = sheet.getRange(“Data!A1:A”).getValue();
var follower_count = sheet.getRange(“Data!B1:B”).getValue();
var date = sheet.getRange(“Data!C1:C”).getValue();
// identifies and adds data to Database
var data1 = ss.getSheetByName(“Database”);
var aVals = data1.getRange(“A1:A”).getValues();
var aLastRow = aVals.filter(String).length;
Logger.log(aLastRow);
var newAttData = [[url,follower_count,date]];
Logger.log(newAttData);
data1.getRange(aLastRow + 1,1,1,3).setValues(newAttData);
}
This works fine for saving the first row of the Data sheet in the database, but I would need it to save multiple rows (at least 7, 1 per day) in the database.
Hope you can help me with this !
Thanks
Thomas. Did you ever figure this out?
Hi Ben,
How can I get multiple data’s from website ?
For example my code is,
function getData() {
var url = “https://www.reddit.com/r/DigitalMarketing/”;
var fromText = ”;
var toText = ”;
var content = UrlFetchApp.fetch(url).getContentText();
var scraped = Parser
.data(content)
.from(fromText)
.to(toText)
.build();
Logger.log(scraped);
return scraped;
}
function SAVE_DATA() {
var sheet = SpreadsheetApp.openById(‘1No3m_FnhyxIaxj2zSlbHrg8HLBJULGQ2bda65hpKlyY’).getSheetByName(‘Scraper’);
var data = getData();
sheet.appendRow([ new Date(), data ]);
}function getData() {
var url = “https://www.reddit.com/r/DigitalMarketing/”;
var fromText = ”;
var toText = ”;
var content = UrlFetchApp.fetch(url).getContentText();
var scraped = Parser
.data(content)
.from(fromText)
.to(toText)
.build();
Logger.log(scraped);
return scraped;
}
function SAVE_DATA() {
var sheet = SpreadsheetApp.openById(‘1No3m_FnhyxIaxj2zSlbHrg8HLBJULGQ2bda65hpKlyY’).getSheetByName(‘Scraper’);
var data = getData();
sheet.appendRow([ new Date(), data ]);
}
If I run this code I am getting only one heading But I want to get all heading in the website. Is there any way to do like that ? Please help me.
Hey Ben
i wanted to know how the log data can be written in google spreadsheet?
basically i am fetching the fire base real-time data to spreadsheet.
the problem is that with the help of google script oi am able to read the data at logs then now how to sort it to get on spreadsheets.
Hi Ben,
First of all, I would like to thank you for your straightforward guide.
Just one question.
How can I specify the starting point of appending new data? For now, the new data are added in columns A, B, and C. But, I need to see the results in other columns. For example, the first entry goes to D4,E4,F4 and the next goes to D5,E5,F5, the next update goes to D6,E6,F6 and ….
Thanks again
Hi Ben,
One useful feature to add to this post, is to have an automatic column with the GoogleID (email) that used the “SaveData” menu to enter new data (the way Google Forms does it). The idea behind this is to be able to track WHO entered the data, can you explain how to modify th script to do this?
Thank you!
Hello and thanks for this amazing solution.
My question is this:
Is it possible to edit the script the way that it saves rows just when a field is equal to a value ?
For example
ROW 1 is 1|2|3|4|OK
ROW 2 is 5|6|7|8|NO
ROW 3 is 9|1|2|3|OK
ROW 4 is 4|5|6|7|NO
I want to save just rows where the fifth column is OK.
Is it possible ?
Great tutorial!
I implemented everything and ran the script but nothing is added. I also read the comments about running the onOpen function but that dosen’t help either. Everything is your original code.
Thanks!
For anyone not getting this to run, try this. I did some minor changes.
// custom menu function
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(“Your awesome Menu”)
.addItem(“Save Data”,”saveData”)
.addToUi();
}
// function to save data
function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName(“Sheet1”);
var url = sourceSheet.getRange(“A1”).getValue();
var follower_count = sourceSheet.getRange(“B1”).getValue();
var date = sourceSheet.getRange(“C1”).getValue();
sourceSheet.appendRow([url,follower_count,date]);
}
Hi Ben,
Your script and explanation was very useful.
I have been trying to assign the column dynamically as given below but always received “Range Not Found” error
var lc=sheet1.getLastRow();
var clf = sheet1.getRange(‘A’&lc).getValue();
I want to use the last row number and the execute the code in loop to save all the rows data. Can it be achieved in that way?
Hello and thank you for the great work you’ve done for the public. (I recently checked one of your xml guides, kudos on the high Alexa ranking!)
I like a challenge but I’m out of my element with GAS/JS. The following needs to be spiced up:
function upMetrics() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getRange(‘Metrics!D17’);
source.copyTo(ss.getRange(‘Metrics!Z2’), {contentsOnly: true});
source.clear ();
}
I can’t implement a .GetLastRow in there properly. I need the sheet to enter data in the next empty row of col Z. e.g. if Z2 is full, paste to Z3, if Z3 is, paste to Z4…
Save me Ben, you’re my only hope!
Hello All,
I have a google sheet that works as a dashboard for me and has data from multiple sheets using query or improtrange. When i create a backup of the file on the 1st of the month thru script and trigger – the back up has #ref error. I want to make a backup of the data without any formulas (As data). I have tried using various codes found online but no avail.
Can you help making a copy of the workbook with all sheets and there data as value – not as formula)
Thanks and regards
Anuj Gupta
This is awesome. Thank you so much for posting. I’ve signed up for a few of your courses and I’m slogging through them… but alas, it takes time…
Question, can the appendRow function be set up to save in an external google sheet file? I actually would love to use a similar process where three people are using different sheets, and they all dump their saved data into a single unified google sheet. Is this possible?
I just found a comment above where you discuss “SpreadsheetApp.openByUrl”… I’m going to give that a try. Thanks!!!
Ok… I’m using this and loving it. Thank you so much!
I used this technique to combine data from 10 staff member data sets, into a single ongoing sheet, and then use that data set to connect to my project management system via API to auto populate new projects. This has been a huge timesaver. Thanks so much Ben!
Just so you know, I’ve signed up for a few of your courses, but haven’t gone through them yet. I need more time in the day! 🙂
Mark
Hi there!
I cant seem to get my script to work. My link is here: https://docs.google.com/spreadsheets/d/1FCrzmbmRH1LU-BfpM6xad6Uff6E4zr3VV4ayUUdxDzU/edit?usp=sharing
I am trying to capture the highlighted area. Can you help?
I want to change the append row location also want to update the data in columns only result.
1 2 3
1 2 3
1 2 3
1 2 3
I want to append-only ‘2’ in columns
so please help me
When i apply this code
// 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.getActiveSheet()
var url = sheet.getRange(“A2:A6”).getValues();
var follower_count = sheet.getRange(“B2:B6”).getValues();
var date = sheet.getRange(“C2:C6”).getValues();
sheet.appendRow([url,follower_count,date]);
}
getting this error “[Ljava.lang.Object;@19bdc45f”
kindly help me.
Hi Ben,
I am trying to utilize your script to save a pre-defined fixed set of column data from one spreadsheet to the next empty column in another spreadsheet and wrote the following, however it is giving me a error that it cannot convert, may I check with you do u know what went wrong?
function saveHerData() {
var sheetfrom = SpreadsheetApp.openById(‘1-12G2zQgwiajIEBkJoHmzxKsWy-b1fd22rTzNgUJfOM’).getSheetByName(“X”);
var sheetto= SpreadsheetApp.openById(‘1dVbemwTmwwV08tgAy75w1Azon3983_gn7Tk6HKzucF4’).getSheetByName(“XX”);
var valuesToCopy = sheetfrom. getRange(“D7:D84”).getValues();
sheetto.getRange(10,sheetto.getLastColumn()+1,valuesToCopy,10).setValues(valuesToCopy);
}
hi guys, need some help im not good in java script but i want to append all the data range column from A2:A, B2:B, C2:C, this code is pulling 1 row only
// custom menu function
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Save Data’,’saveData’)
.addToUi();
}
function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1]
var lastname = sheet.getRange(‘Sheet1!A2:A6’).getValue();
var firstname = sheet.getRange(‘Sheet1!B2:B6’).getValue();
var middlename = sheet.getRange(‘Sheet1!C2:C6’).getValue();
sheet.appendRow([lastname,firstname,middlename]);
}
You can Easily Save Scheduled Data using “Integromat” automation web app. just use the function “add row” in Integromat and put the formula and schedule this Scenario to run once a week for example.
Thanks so much Ben this is incredible. I stumbled on this second article after using your Social Listening article to create my own tool over a year ago and now’s the time when I can finally deploy it. Both are a testament to how to explain things well with a step by step process that any old muppet could follow.
Thanks, Toby! Glad to hear it’s been helpful. Cheers.
Hi Ben, how are you?
First, great tutorial. It helped a lot to understand several doubts.
I’m starting now with scripting on Google spreadsheets and I’m a little lost …
I use an IMPORTXML (“https://statusinvest.com.br/acoes/ABEV3”; “//div[@title=’Valor atual do ativo’]/strong”) that searches for a stock quote. So far, so good, but when using many IMPORTXML in the same spreadsheet, it started to be very slow and often does not bring the values.
I would like to do the same thing, but via script, automatically filling a column with the quotation of each stock in column B, as I believe it will keep the spreadsheet optimized, only that I always get an error of lack of permission to run any command, either for getRange() or setFormula().
Could you please guide me on the correct way to work with IMPORTXML and XPATH in a script code?
Thank you very much for your attention and once again, congratulations on your work.
Hi Marcelo, are you still able to use that formula? I’m trying to use the same and another similar, but i can’t, it is returning an error when i only copy and past your formula that the url could not be found.
Não foi possível encontrar o URL: https://statusinvest.com.br/acoes/ABEV3
Not sure why, maybe they have blocked something in the statusinvest server?? Did you manage to create the script?
Cheers!
Hi Ben,
has your twitter follower scrape stopped working?
Because mine was pulling fine until a couple weeks ago.
Hi
I got the following message while applied the above mentioned script for auto saving.
This app isn’t verified
This app hasn’t been verified by Google yet. Only proceed if you know and trust the developer.
Could you please give some ideas how to verify it? I am the beginner learner for this.
Hi Ben,
it was so useful.
tnx
Hmmm. Don’t see anyone else with this issue, but I can add the Custom Menu, but when I click the ‘save data’ button, I get an authorization screen. When I try to authorize with my google account, it tells me “The App is Blocked: This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.”
No way to get past this screen. I do have two-factor turned on, but can’t seem to google my way out of this problem. Any ideas?
Hi Ben, thanks for this post – very helpful!
I’ve successfully created a time-driven trigger which fills the next row in my spreadsheet, but it seems the function fails as I get a NAME? error (I’m guessing the function in the Apps Script isn’t fully loaded before the spreadsheet opens and runs the trigger). Any advice on how to mitigate? Thanks.
Thank you Ben!
Great post and helpful tips in comments. You saved my day and open me totally new rabbit hole to investigate more.
Hi Ben,
Thank you for this excellent explanation.
I have taken your script an amended it slightly as I have a sheet that scrapes investment prices from a few websites and then the save data script saves the downloaded data to 3 separate sheets depending on investment type. This all works well with your instruction.
I have one question regarding using a time trigger. Will the trigger allow the sheet that scrapes the data to refresh before the script runs or do I need to script a refresh of this sheet?
Regards
Ian
Thanks for this it helped me a lot in a custom Sheets I’m using…question: is there a way to have a rolling 10 values appendrow function ?
I’d like to save a value a day via “Trigger” but I’d like to keep just the last 10 pushing the old ones… away.
Hi, great work. I tried to use it on my data but I end up with Ljava.lang.Object;@…
How should I change it from an Array to a String?
Hello Ben,
First of all, congrats for this amazing script. I am a new user in Google Scripts and it solved a big issue that i had in a inplementation of google sheets problem. Suddenly, yesterday, script stopped working as expected. I haven’t modified anything comparing with version that was functional. It hapoens a lot wirh Google scriots? Any toughts about what happened? Thank you a lot for creating this huge knowledge community.
Regards,
Rodrigo
Hi Ben,
This is very helpful. Thankyou you so much.
I would like to save the values from A4 to Z4 in the next row, but it is picking only A4 cell and for rest it is showing empty. Please help me understand what I am missing.
function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(“History”);
var all_data = sheet.getRange(“A4:Z4”).getValue();
sheet.appendRow([all_data]);
}
get the values from spreadsheet and then update the first name and last
name in admin console and add a column in spreadsheet whether the status is completed or got
error
Hi, Ben, Best Regards to you for such a great recording process. Pl help me with how to record data in reverse order i.e fresh recorded data should be top.
Hi Ben, thank you very much for the content, if my data is not the website these are from the document do you have an idea how to do it
Hi Ben,
Your Blog is great and the code is super helpful.
yet it looks bit old for the Apps Script 2022 updates.
The code runs, the button/custom menu appears but no action happens when save data button is clicked.
Would you please update us with the new code for the same basic functions ?
Thanks
beautiful post! is it possible to save a range of data, for example instead of B1 if there was the range B1:AC1?