This should get you started: Using VBA in your own Excel workbook, have it prompt the user for the filename of their data file, then just copy that fixed range into your target workbook (that could be either the same workbook as your macro enabled one, or a third workbook). Here’s a quick vba example … Read more
SimpleXLSXGen $books = [ [‘ISBN’, ‘title’, ‘author’, ‘publisher’, ‘ctry’ ], [618260307, ‘The Hobbit’, ‘J. R. R. Tolkien’, ‘Houghton Mifflin’, ‘USA’], [908606664, ‘Slinky Malinki’, ‘Lynley Dodd’, ‘Mallinson Rendel’, ‘NZ’] ]; $xlsx = Shuchkin\SimpleXLSXGen::fromArray( $books ); $xlsx->saveAs(‘books.xlsx’); // $xlsx->downloadAs(‘books.xlsx’);
No need to extact the value to determine if the cell is empty. Google Spreadsheet API already has a method for this: Range – isBlank method var cell = r.getCell(rws-1, 10); if (cell.isBlank()) { cell.setValue(“foo”); }
Unfortunately there is no searching functionality in the Spreadsheet services. You can get the data for the range you are searching on, and then iterate over it looking for a match. Here’s a simple function that does that: /** * Finds a value within a given range. * @param value The value to find. * … Read more
This is an almost identical solution that I posted about a month ago: Excel VBA Insert Images From Image Name in Column Sub InsertPic() Dim pic As String ‘file path of pic Dim myPicture As Picture ’embedded pic Dim rng As Range ‘range over which we will iterate Dim cl As Range ‘iterator Set rng … Read more
I suppose the problem is, just how complex is your “complex structure”? Programming IDEs such as Oracle SQL Developer or Quest TOAD have wizards to export data table to CSV files. If you want to join data from multiple tables you could use a view, or even write a SQL statement select e.ename||’,’||d.dname from emp … Read more
So I finally figured out what was going on. Through messing with manually encoding answers to the end of the form POST url I was able to find that the url it gave when viewing the source had encoding issues of it’s own in it. Here’s the url from source: <form action=”https://spreadsheets.google.com/spreadsheet/formResponse?hl=en_US&formkey=dDlwZzh4bGFvNFBxUmRsR0d2VTVhYnc6MQ&ifq” method=”POST” id=”ss-form”> But … Read more
Try looking at PHPExcel. This is a simple example that creates an Excel file with two sheets: <?php require_once ‘PHPExcel.php’; require_once ‘PHPExcel/IOFactory.php’; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Create a first sheet, representing sales data $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue(‘A1’, ‘Something’); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle(‘Name of Sheet 1’); // Create a new worksheet, after … Read more
first, let’s see what says the fox =WHATTHEFOXSAY() is a unique easter egg google sheets function (discovered by @kishkin) that randomly generates a pre-set string of text on user demand which is a huge deal because while the generation is random, the recalculation is not affected by onEdit, onChange nor onOpen events, so with some … Read more
INDEX/MATCH will do it any direction of search. So for your example of B –> A: =INDEX(A:A,MATCH(yourCriteria,B:B,0)) The MATCH returns the row number of the match. The third Criterion of 0 is optional. The 0 is the same as FALSE for the forth criterion of VLOOKUP, in that it looks for an exact match. The … Read more