Saving data in Google Sheets with Google Apps Script

In a different post, I looked at importing social media data into a Google Sheet. Since then, I’ve had a few readers contact me to ask if there’s a way to save the imported data, so that you have a record of the data at set time intervals (e.g. once a day or once a week).

The answer is: Absolutely!

It involves writing a short script but it’s pretty simple and we can even set up the whole shebang to run automatically in the background.

save data in google sheet

This functionality plays a big part in my online course when we save social media metrics for the digital marketing dashboard project. Check it out here:

Build Business Dashboards With Google Sheets and Data Studio

Digital marketing dashboard in Google Sheets
Learn how to build beautiful, interactive dashboards in my online course.
9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
Learn more

Creating a Save Data function with Google Apps Script

Ok, so here’s the scenario: I’m using Google Sheet’s IMPORT() formulas to get hold of follower counts for a social media channel (in this case, the number of followers of the Reddit Space group). This is running in cells A1 and B1. The formula to get the Reddit followers is:

=importxml(A1,"//span[@class='subscribers']/span[@class='number']")

For more details, see my other post on importing various different social media channel metrics into Google Sheets.

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:

  1. 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:
  2. Import social media followers into Google Sheet

  3. Type headings into row 4, as shown in this image:
  4. headings

  5. Next go to Tools > Script editor…
  6. This opens a new tab in your browser. Remove the existing code in the main window (the bit of code: function myFunction() { }).
  7. Paste the following code into the window:
  8. 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.

  9. Hit save.
  10. Return to your spreadsheet window.
  11. Reload your spreadsheet (refresh your browser) and you should see a new menu: Custom Menu > Save Data item
  12. Run this. First time it’ll ask you for permission so click Allow.
  13. Google Sheet App Script Permission

  14. When it runs, it will append a new row beneath your existing entries with a copy of the data from row 1.
  15. You can now re-run this whenever you want and it’ll save a copy of the current values and timestamp below.

saved data in Google sheets

Cool huh?!?

But what if we forget to do it one day, or we’re inconsistent with our time periods. It’ll result in patchy data.

It’d be much better if we could automate it to run at set intervals.

Thankfully we can, and it’s super easy with Google Sheets.

Automatic Data Saving

Steps for automatic data saving:

  1. Go to back to your script editor (Tools > Script editor… if you closed that tab).
  2. Click on Resources > Current project’s triggers
  3. This brings up the triggers pane. In this pane, click on No triggers set up. Click here to add one now.
  4. In the first drop down, select the Save Data function, as shown in this image:
  5. Function trigger in Google Apps Script

  6. For the remaining drop down options, select Time Driven and then the period you’re interested in.
  7. 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!

40 thoughts on “Saving data in Google Sheets with Google Apps Script”

  1. 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,

    1. 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.

      1. 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!

        1. 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.

          1. 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.

  2. 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.

    1. 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

      1. 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.

        1. Hey Jason,

          You can use getRange() and then setValues() 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 handy getLastRow() 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 data
          function 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

  3. 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

    1. 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

      1. 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

  4. 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.

  5. 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!

    1. 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 data
      var 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

  6. 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

  7. 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!

  8. 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

    1. 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/bitcoin to trigger a refresh…

      Cheers,
      Ben

      1. Hello Ben,

        Thanks for great tip. Works like charm.
        Sorry it was indeed a double formula.
        But it works.

        Thanks for your help.

        John

            1. Hi John,

              I used the Chrome Developer Tools to look at the underlying HTML/CSS for the website, as in this image:

              Bitcoin IMPORT query id

              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

  9. 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]);
    }

    1. 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

    2. 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

  10. 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

  11. 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?

  12. 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

  13. 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

    1. 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

    1. 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.

  14. 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!

    1. 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

Leave a Reply to John Slegers Cancel reply

Your email address will not be published. Required fields are marked *