Sync/Export orders to google spreadsheet

Can anyone help with a solution through script or extension to automatically export/sync data to google spreadsheets such that whenever an order is placed, it instantly adds the data to the sheets on my drive as well. I have tried zapier for this purpose but it has task limits and also has 5 minutes intervals in between. i want a method to do this process instantly.

Is there a way to send an email once a spreadsheet is uploaded/imported to a database?

I’m not sure if this is the right place to ask this question, but is there a way to send an email to multiple recipients via PHP on the trigger of an Excel spreadsheet being uploaded/imported to a MySQL table.

Do I need to create a trigger in MySQL to do this?

I’m using a MySQL 5.6 and PHP 5.

How to read * .tsv files from a local drive into a spreadsheet file using Apps Script?

I have a *.tsv file in local storage on my computer. I want the file to be read from the apps script then the contents of the file are copied into a spreadsheet file. I do not want the file to be uploaded first. This concept is like copy-paste. enter image description here The script that I have created is like this:

function importTSVFromLocal() {    // Provide the full URL of the CSV file.   var tsvUrl = "file:///C:/Users/{username}/Downloads/Test1%20-%20Sheet1.tsv";   var tsvContent = UrlFetchApp.fetch(csvUrl).getContentText();   var tsvData = Utilities.parseCsv(csvContent);    var sheet = SpreadsheetApp.getActiveSheet();   sheet.getRange(1, 1, tsvData.length, csvData[0].length).setValues(csvData);  } 

But it looks like the script that I created doesn’t work. I got an error message :

Bad host name: file:///C:/Users/{username}/Downloads/Test1%20-%20Sheet1.tsv (line 5, file “Code”)

Filter spreadsheet by user-entered value in first row

In Google Sheets, I want to setup a filter view like this.
Row 1 = column headings
Row 2 = filter criteria
Rows 3… = data

I want the user to type a value into any column of row 2, and this will dynamically update which rows are displayed in the filter view. Is this possible? For example starting with column C, I enter “Disney” into C2, then I enter this formula as a filter, but it returns only row 2.

But if I enter it as =REGEXMATCH(C2, "Disney") then it correctly returns all rows with Disney in column C.

How to timestamp on new added cells in LIVE google spreadsheet?

The code below insert timestamp in google sheet by editing another column. However, there an issue which I have no idea how to solve. When I add new row manually, it successfully adds timestamp, BUT I have an html form in my website which sends data to this sheet and even-though new row is added, google don’t create a timestamp !!! It’s a bit weird since conceptually when a new row is added, means an edit in cell, and google must add timestamp. I would be so grateful if someone help me to solve this issue. Thanks

function onEdit(event) {    var timezone = "GMT+8:30";   var timestamp_format = "yyyy-MM-dd HH:mm:ss"; // Timestamp Format.    var updateColName = "date";   var timeStampColName = "Date Sent";   var sheet = event.source.getSheetByName('Attendance'); //Name of the sheet where you want to run this script.     var actRng = event.source.getActiveRange();   var editColumn = actRng.getColumn();   var index = actRng.getRowIndex();   var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();   var dateCol = headers[0].indexOf(timeStampColName);   var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;   if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!     var cell = sheet.getRange(index, dateCol + 1);     var date = Utilities.formatDate(new Date(), timezone, timestamp_format);     cell.setValue(date);   } } 

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following code:

function onEdit(event){     var sheet = event.source.getActiveSheet();   var editedCell = sheet.getActiveCell();    var columnToSortBy = 1;   var tableRange = "A2:B500";    if(editedCell.getColumn() == columnToSortBy){         var range = sheet.getRange(tableRange);       range.sort( { column : columnToSortBy } );   } } 

However, when I run it, I get the following message: Cannot read property “source” from undefined. (line 2)

Could someone please help me figure out what I am doing wrong?

How can I apply a script to only one specific sheet within a spreadsheet?

I have a spreadsheet with two sheets called Robin and Lucy. I’ve made/found/mangled a script to sort the data on the sheet each time I add some data to column A:

function onEdit(event){    var sheet = event.source.getActiveSheet();   var editedCell = sheet.getActiveCell();    var columnToSortBy = 1;   var tableRange = "a2:I30";     if(editedCell.getColumn() == columnToSortBy){       var range = sheet.getRange(tableRange);    range.sort( { column : columnToSortBy } );   } } 

This script works well but I only want it it be applied on the first sheet, Robin. The data in the second sheet, Lucy, isn’t the same so I’m going to create another script for a different range for that one, once I get my head around this problem.

I think I need to use the getSheetByName("Robin") but I can’t seem to get it to work.

Jump to bottom of spreadsheet, blank (non-formula) row

I had a tall spreadsheet that I need to add data at every time it opens. Answers like Go to Bottom Cell have been helpful, but my quirk is that the first column contains formulas that return blank in most situations; therefore getLastRow() returns the last row in the entire sheet, not the last blank row.

Here’s my solution, that works well except for one quirk.

function onOpen() {   var mysheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();   var dataRange = mysheet.getRange("A:A");   var lastrow = dataRange.getLastRow();   var dVal="";    for (var i=lastrow; dVal==""; i--) {     dVal=dataRange.getCell(i,1).getDisplayValue();   }     mysheet.setActiveCell(dataRange.offset(i,0,1,1)); }; 

When the spreadsheet opens, the active cell is in the right spot (cell A4678) but the screen is displaying cells A4533-A4562. If I hit the down key, the screen refreshes and I am exactly where I need to be.

So how do I get the screen to refresh and show me the cursor in the active cell?

Edit: Further details, as requested (thx @Rubén):

  • First sheet has 4728 rows. First 4679 rows have data, therefore 1st blank row is 4680
  • 6 sheets in spreadsheet. Sheet 1 is the largest one, the rest use data from this sheet and analyse it in different ways
  • Click-to-ready time is about 8 seconds using the optimized formula suggested by @if…