Script to populate Calc fields from the web

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
othermeans
Posts: 2
Joined: Tue Mar 30, 2021 11:57 pm

Script to populate Calc fields from the web

Post by othermeans »

I'm trying to use this script editor to populate the fields in Calc. What it is supposed to do when you enter the ISBN Number for a book it is supposed to enter the title, publisher, author and date. It would be an incredible timesaver for me. I just don't know how to go about it.
Here is a copy of the script:

Code: Select all

s = SpreadsheetApp.getActiveSheet();

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book Menu')
      .addItem('Get Book Details', 'getBookDetails')
      .addToUi();
}

function getBookDetails(isbn) {
  
  // Query the book database by ISBN code.
  activeCell = s.getActiveCell();
  value = activeCell.getValue();
  isbn = isbn || value.toString(); // Steve Jobs book 
 
  // Not a valid ISBN if not 13 or 10 digits long.
  if(isbn.match(/(\d{13}|\d{10})/) == null){
    throw new Error( "Not a valid ISBN: " + isbn);
  }
  var url = "https://www.googleapis.com/books/v1/volumes?q=isbn:" + isbn;
  var response = UrlFetchApp.fetch(url);
  var results = JSON.parse(response);
  if (results.totalItems) {

    // There'll be only 1 book per ISBN
    var book = results.items[0];

    var title = (book["volumeInfo"]["title"]);
    var subtitle = (book["volumeInfo"]["subtitle"]) || "*No Subtitle";
    var authors = (book["volumeInfo"]["authors"]);
    var printType = (book["volumeInfo"]["printType"]);
    var pageCount = (book["volumeInfo"]["pageCount"]);
    var publisher = (book["volumeInfo"]["publisher"]);
    var publishedDate = (book["volumeInfo"]["publishedDate"]);
    var webReaderLink = (book["accessInfo"]["webReaderLink"]);

    //Logger.log(book);
    results = [[title, subtitle, authors, printType, pageCount, publisher, publishedDate, webReaderLink]];

  }else{
    results = [["-", "-", "-", "-", "-", "-", "-", "-"]];
  }
  s.getRange(activeCell.getRow(), activeCell.getColumn() + 1, 1, results[0].length).setValues(results);
}
Last edited by MrProgrammer on Wed Mar 31, 2021 8:20 pm, edited 2 times in total.
Reason: Added Code tags; Moved from Calc forum to Macros and UNO API where you are more likely to find people to help with macros
Libre 7.1.1 OS is Windows 10 64 bit
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Using a script editor to populate fields in Calc

Post by RusselB »

Welcome to the Forums.
Personally for a project like this I would recommend a database, not a spreadsheet, but I suspect that doing your "spreadsheet script" for a database would require a re-write, possibly from scratch.
Off topic, I note that your signature indicates OpenOffice 7.1.1. That version hasn't been released yet. If you mean LibreOffice 7.1.1 or if you meant a different version of OpenOffice, please use the User Control Panel to update your signature with the correct information.
It is important that we have the correct information, as there are differences between the two programs and an incorrect program identifier can lead to incorrect information being presented to help resolve a problem.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
othermeans
Posts: 2
Joined: Tue Mar 30, 2021 11:57 pm

Re: Script to populate Calc fields from the web

Post by othermeans »

Hi thanks for letting me know about my mistake. I just updated it. I'm not very knowledgeable regarding working with spreadsheets I thought it would just be convenient way to catalog my books. I'll try finding something that might work. Thanks again
Libre 7.1.1 OS is Windows 10 64 bit
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Script to populate Calc fields from the web

Post by Sébastien C »

Hello to all,

It seems to me that the problem presented here is less focused on using a database rather than a spreadsheet as it is, I think, a bit too rehashed.
For starters, the Basic posted here by "othermeans" is not LibreOffice Basic but VBA from YouKnowWho. And the problem it exposes is, mainly to be able to read JSON format sent by the API of Google.

Now, there is an extension that does exactly this job.
Get the OXT file and install the extension like any other.
The extension adds two new functions to LibreOffice Calc described here.

This is all well and good, but the fact remains that the Google API must also be understood. The abundance of documentation for the plugin isn't what will fill your personal library, either. As we say in French: « bon courage »...
Likewise, imagine that for each field (Title, Author, Publisher, &c.) of each record, there will be a call. And that very quickly becomes unmanageable.
For only four books, the time it takes to fill the cells, when the file is opened (given here as an example), the connection time is quite long.
On the other hand, to consider copying and pasting the data received, the spreadsheet remains very flexible. It is also with this type of test that we realize that Google is only a help and also contains either mistakes, or (especially) shortcomings.

If you read French, a great little tutorial can be found here.
Attachments
PARSEJSON.ods
Requires LibreOfficeGetRestPlugin library
(8.31 KiB) Downloaded 262 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Script to populate Calc fields from the web

Post by Sébastien C »

A little more, or more exactly, a way of doing things differently.

Enter the ISBN or the bar code of the book in cell B3 of the "Datas" page. This means that it is therefore possible to enter this value via a barcode scanner (EAN 13)... Once entered, the call to the Google’s API is done naturally and the cells C3 to H3 fill normally. Sometimes a field is not filled; the word “None” is therefore placed in the concerned cell. A conditional format emphasizes this lack, just to make it clear.

There is in cell A3 a hyperlink “Copy in list” which calls a macro which will automatically make the copy of the useful cells of the line 3 in a list located just below. The addition is always done at the end of the list. In this collection, therefore, there is no longer any unnecessary calls, but simply storage, which can be modified or supplemented and, of course, eventually transformed into a real database. AutoFilters still allow you to quickly sort the list, which can be very practical for grouping works with the same content (typically: “None”).

Finally, we will easily notice the advantage of freezing the six first lines, which allows, of course, to always have in view the end of the list on which we make the copies.

Enjoy!
8-)

Code: Select all

Option Explicit

Const myZone1 = "$B$3:$H$3" ' Range where the fields of the book are downloaded.

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Copy the data range myZone1 to the bottom of the list.                           ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub copyInList()
 Dim   mySheet As Object, myFunctionAccess As Object, myDataZone1 As Object, myDataZone2 As Object
 Dim myArgs(3) As Variant
 Dim   myZone2 As String

      myFunctionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")

             myArgs(3) = knowLastCell("Datas", 1, 0) + 2

             myArgs(0) = myZone1 : myArgs(1) = 4            : myArgs(2) = 1
               myZone2 = myFunctionAccess.callFunction("REPLACE", myArgs())
             myArgs(0) = myZone2 : myArgs(1) = len(myZone2) : myArgs(2) = 1
               myZone2 = myFunctionAccess.callFunction("REPLACE", myArgs())

               mySheet = thisComponent.sheets.getByname("Datas")
           myDataZone1 = mySheet.getCellRangeByName(myZone1)
           myDataZone2 = mySheet.getCellRangeByName(myZone2)

 myDataZone2.dataArray = myDataZone1.dataArray
End Sub


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Search the number of the last line with something in cell in the column.         ║█
' ║ or the number of the last column with something in cell in the line              ║█
' ║                                                                                  ║█
' ║ If myType = 0, the search is do on the column.                                   ║█
' ║ If myType = 1, the search is do on the line.                                     ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Function knowLastCell(mySheetName As String, myColumnOrMyLine As Long, myType As Integer) As Long
  Dim mySheet As Object, myBlock As Object, myFound As Object

  On Error Goto myError
  mySheet = thisComponent.sheets.getByName(mySheetName)

  Select Case myType
   Case 0 : myBlock = mySheet.columns(myColumnOrMyLine)
   Case 1 : myBlock = mySheet.rows   (myColumnOrMyLine)
  End Select
   
  myFound = myBlock.queryEmptyCells.rangeAddresses

  With myFound(uBound(myFound))
   Select Case myType
    Case 0 : knowLastCell = .startRow    - 1
    Case 1 : knowLastCell = .startColumn - 1
   End Select
  End With

  Exit Function

  myError:         ' If the column or the line is empty.
  knowLastCell = -1
End Function
Attachments
PARSEJSON_v1.ods
Requires LibreOfficeGetRestPlugin library
(11.67 KiB) Downloaded 255 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply